Centos7.4 how to compile and install mysql using cmake

Posted by riex1 on Wed, 09 Mar 2022 10:38:40 +0100

Basic overview

Introduction to mysql

Deployment environment

CentOS Linux release 7.4.1708192.168.10.10/24

Experimental steps

1. Mysql download address: https://downloads.mysql.com/archives/community/

  • Download installation package: https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.20.tar.gz

    [root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.20.tar.gz
    --2021-04-07 09:48:21--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.20.tar.gz
    Resolving downloads.mysql.com (downloads.mysql.com)...
    Connecting to downloads.mysql.com (downloads.mysql.com)||:443... connected.
    HTTP request sent, awaiting response... 302 Found
    Location: https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.20.tar.gz [following]
    --2021-04-07 09:48:23--  https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.20.tar.gz
    Resolving cdn.mysql.com (cdn.mysql.com)...
    Connecting to cdn.mysql.com (cdn.mysql.com)||:443... connected.
    HTTP request sent, awaiting response... 200 OK
    Length: 32979820 (31M) [application/x-tar-gz]
    Saving to: 'mysql-5.6.20.tar.gz'
    100%[=========================================>] 32,979,820  9.70MB/s   in 3.2s   
    2021-04-07 09:48:26 (9.70 MB/s) - 'mysql-5.6.20.tar.gz' saved [32979820/32979820]
    [root@localhost ~]# 

2. Unzip the installation package

  • tar xf mysql-5.6.20.tar.gz

    [root@localhost ~]# tar xf mysql-5.6.20.tar.gz
    [root@localhost ~]# ls
    anaconda-ks.cfg  mysql-5.6.20  mysql-5.6.20.tar.gz  perl5  yum_back
    [root@localhost ~]# 

3. Install dependency tools (GCC C ncurses ncurses devel cmake libaio bison gcc-c + + GIT)

  • yum install

    [root@localhost ~]# yum -y install gcc c ncurses ncurses-devel cmake libaio bison gcc-c++  git 

4. Precompiling with cmake

  • Cmake is a cross platform compilation tool, which is more advanced than configure. Cmake mainly writes cmakelists Txt, and then use the cmake command to convert cmakelists Txt into the Makefile file required by make, and finally compile the executable generated by the source code with the make command.

  • Cmake tool and configure tool. The configure binary program is used to generate makefile files. Makefile records the environment required by the software and the parameters to be compiled

    [root@localhost ~]# cd mysql-5.6.20
    [root@localhost mysql-5.6.20]# ls
    BUILD            dbug                 libmysqld    regex          unittest
    BUILD-CMAKE      Docs                 libservices  scripts        VERSION
    client           Doxyfile-perfschema  man          sql            vio
    cmake            extra                mysql-test   sql-bench      win
    CMakeLists.txt   include              mysys        sql-common     zlib
    cmd-line-utils   INSTALL-SOURCE       mysys_ssl    storage
    config.h.cmake   INSTALL-WIN-SOURCE   packaging    strings
    configure.cmake  libevent             plugin       support-files
    COPYING          libmysql             README       tests
    [root@localhost mysql-5.6.20]# cd
    [root@localhost ~]# 
    [root@localhost ~]# cd mysql-5.6.20
    [root@localhost mysql-5.6.20]# cmake  .  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql55/ \
    > -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
    > -DMYSQL_DATADIR=/data/mysql \
    > -DSYSCONFDIR=/etc \
    > -DMYSQL_USER=mysql \
    > -DMYSQL_TCP_PORT=3306 \
    all \
    > -DDEFAULT_CHARSET=utf8 \
    > -DDEFAULT_COLLATION=utf8_general_ci \
    > -DEXTRA_CHARSETS=all \
    > -DWITH_DEBUG=0

5. make compilation

  • make -j4

    [root@localhost mysql-5.6.20]# make -j4

6,make install

  • make install install

    [root@localhost mysql-5.6.20]# make install -j4

7. Set profile

  • create profile

    [root@localhost mysql-5.6.20]# cd /usr/local/mysql55/
    [root@localhost mysql55]# ll support-files/
    total 32
    -rwxr-xr-x 1 root root  1153 Apr  7 20:08 binary-configure
    -rw-r--r-- 1 root root   773 Jul 18  2014 magic
    -rw-r--r-- 1 root root  1126 Apr  7 20:08 my-default.cnf
    -rwxr-xr-x 1 root root  1061 Apr  7 20:08 mysqld_multi.server
    -rwxr-xr-x 1 root root   880 Apr  7 20:08 mysql-log-rotate
    -rwxr-xr-x 1 root root 10880 Apr  7 20:08 mysql.server
    [root@localhost mysql55]# \cp -f support-files/my-default.cnf /etc/my.cnf
    [root@localhost mysql55]# \cp -f support-files/mysql.server /etc/init.d/mysqld 

8. Set service startup

  • test

    [root@localhost mysql55]# chkconfig --add mysqld
    [root@localhost mysql55]# chkconfig --level 35 mysqld on

9. Create users, directories, and modify permissions

  • /data/mysql database data storage location

  • Create mysql user

  • Modify permissions

    [root@localhost mysql55]# mkdir -p /data/mysql
    [root@localhost mysql55]# useradd -M -s /sbin/nologin mysql
    [root@localhost mysql55]# chown -R mysql.mysql /data/mysql/
    [root@localhost mysql55]# /usr/local/mysql55/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql55/
    FATAL ERROR: please install the following Perl modules before executing /usr/local/mysql55/scripts/mysql_install_db:   
    An error is reported here. Install according to the prompt perl*,Then initialize again
    [root@localhost ~]# yum -y install perl*
    [root@localhost mysql55]# /usr/local/mysql55/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql55/
    [root@localhost mysql55]# ln -s /usr/local/mysql55/bin/* /usr/bin / connect the executor under mysql bin to / usr/bin

10. Start up and test

  • service mysqld start

  • /etc/init.d/mysqld stop

  • systemctl start mysqld

    [root@localhost ~]# service mysqld start
    Starting MySQL.. SUCCESS! 
    [root@localhost ~]# 

Detailed explanation of MYSQL database configuration file

  • Common profile parameters

    [mysqld]   											Server side configuration
     datadir=/data/mysql   								Data directory
     socket=/var/lib/mysql/mysql.sock   				socket Communication settings  
     user=mysql 										use mysql User startup
     symbolic-links=0  									Whether shortcuts are supported
     log-bin=mysql-bin  								open bin-log journal
     server-id = 1  									mysql service ID
     [mysqld_safe]  									mysql Service security startup configuration
    key_buffer_size 									Specifies the size of the index buffer, which determines the speed of index processing, especially index reading. Generally 50% of memory%
    show variables like 'key_buffer_size';
    max_connections = 3000
    													Maximum allowable number of errors per client connection,If this limit is reached, this client will be MySQL "Service blocked until" FLUSH  HOSTS"Or service restart.
    													about InnoDB For example, innodb_buffer_pool_size The function of is equivalent to key_buffer_size about MyISAM Tables work the same way. InnoDB Use this parameter to specify the size of memory to buffer data and indexes.
    For individual MySQL The database server can set this value to 80% of the physical memory at most%. 
    according to MySQL Manual, for 2 G Memory machines, the recommended value is 1 G(50%). 
    basedir      = path          					# Use the given directory as the root directory (installation directory).
    datadir      = path         				    # Read database files from the given directory.
    pid-file     = filename      					# Specify a file to store the process ID for mysqld program (only applicable to UNIX/Linux system); 
    socket = /tmp/mysql.sock     					# Specify a socket file for the local communication between MySQL client and server (the default is / var/lib/mysql/mysql.sock file under Linux)
    port             = 3306      					# Specify the port on which MsSQL listens 
    key_buffer       = 384M      					# key_buffer is the buffer size used for index blocks. Increasing it can get a better index (for all reads and multiple rewrites). The index block is buffered and shared by all threads, key_ The size of the buffer depends on the memory size.
    table_cache      = 512       					# The number of tables open for all threads. Increasing this value can increase the number of file descriptors required by mysqld. It can avoid the overhead of frequently opening data tables
    sort_buffer_size = 2M        					# Each thread that needs to be sorted is allocated a buffer of that size. Increasing this value accelerates ORDER BY or GROUP BY operations. Note: the allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the total sorting buffer size actually allocated is 100 × 6=600MB
    read_buffer_size = 2M       					 # The size of the buffer that can be used by read query operations. And sort_ buffer_ Like size, the allocated memory corresponding to this parameter is also exclusive for each connection.
    query_cache_size = 32M       					# Specifies the size of the MySQL query result buffer
    read_rnd_buffer_size    = 8M 					# This parameter is used for random reading after sorting with row pointer.
    myisam_sort_buffer_size = 64M 					# Buffer required to reorder when MyISAM table changes
    thread_concurrency      = 8  					# The maximum number of concurrent threads. The value is the number of logical CPUs of the server × 2. If the CPU supports H.T hyper threading, then × two
    thread_cache            = 8  					#Number of threads that can be reused in the cache
    skip-locking            						# Avoid external locking of MySQL, reduce error probability and enhance stability. 
    max_allowed_packet      =16M 					# The maximum possible packets that can be sent between the server and the client

MYSQL password management

During the use of MYSQL database, in order to strengthen security, you need to set password access, how to set password, and how to crack if you forget the password. The following methods are used to set password authorization, password modification and cracking.

MYSQL user creation authorization

  • jurisdiction

    Database name.*            All in the database
     Database name.surface           Specify a table in the database
     Database name.stored procedure      Specifies the stored procedure in the database
    *.*                   All databases
    • Authorize the localhost host to access the local ISP library through the huang user and admin123 password;
    mysql> grant all on  ISP.* to huang@localhost identified by 'admin123';
    Query OK, 0 rows affected (0.21 sec)
    • 123 query, update and delete the password of all users through the ISP Library
    mysql> grant select,insert,update,delete on *.* to hun@"%" identified by '123';
    Query OK, 0 rows affected (0.00 sec)
    • Authorize the host to access the local ISP library through huang user and admin123 password;
    mysql> grant all on ISP.* to huang@'' identified by 'admin123';
    Query OK, 0 rows affected (0.00 sec)

MYSQL password cracking method

  • Add skip grant tables in the configuration file [mysqld] module

    [root@localhost ~]# /etc/init. D / mysqld stop 1. Stop the mysql service
    Shutting down MySQL. SUCCESS! 
    [root@localhost ~]# 
    [root@localhost ~]# vim /etc/my. CNF 2. Modify the configuration file
    [root@localhost ~]# cat /etc/my.cnf|grep skip-grant*
    [root@localhost ~]# /etc/init. D / mysqld restart 3. Restart the mysql service,
     ERROR! MySQL server PID file could not be found!
    Starting MySQL.. SUCCESS! 
    [root@localhost ~]# mysql 4. Enter mysql without password
    mysql> use mysql;								  use mysql database
    Database changed
    mysql> update user set password = Password('hebao') where user = 'root';										   5,Change Password
    mysql> flush privileges;						  6,Refresh permissions
    mysql> quit;


MYSQL user actions

Connect database

mysql  -u user -p                   
Example: mysql -u root -p
 Common errors are as follows:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.
Exit connection:
QUIT perhaps Ctrl+D

View, create and use databases, tables

mysql> show databases;
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| test               |
4 rows in set (0.00 sec)

mysql> create database S default charset utf8 collate utf8_general_ci;

mysql> use S;
Database changed
mysql> show tables;
Empty set (0.00 sec)

User operation

  • Create user
 Create user
 create user 'user name'@'IP address' identified by 'password';
 delete user
 drop user 'user name'@'IP address';
 Modify user
 rename user 'user name'@'IP address' to 'New user name'@'IP address';
 Change Password
 set password for 'user name'@'IP address' = Password('New password');
 Note: data related to user authority is saved in mysql Database user Table, so you can also operate it directly (not recommended)
 mysql> create user 'hbs'@'' identified by '123';
 Query OK, 0 rows affected (0.00 sec)
 mysql> drop user 'hbs'@'';
 Query OK, 0 rows affected (0.00 sec)
 mysql> rename user 'hbs'@'' to 'liuaoni'@'';
 Query OK, 0 rows affected (0.00 sec)
 mysql> set password for 'liuaoni'@''=Password('!23');
 Query OK, 0 rows affected (0.00 sec)

MYSQL table operation

  • View table

    show tables; 	 			View all database tables
    select * from Table name;  		   View all contents of the table
  • Create table

    create table Table name(
        Whether the column name type can be empty,
        Can column name type be empty
     Give an example to explain in detail
    CREATE TABLE `tab1` (
      `nid` int(11) NOT NULL auto_increment,
      `name` varchar(255) DEFAULT test,
      `email` varchar(255),
      PRIMARY KEY (`nid`) 
    Default value: the default value can be specified when creating a column. If it is not actively set when inserting data, the default value will be automatically added
     Auto increment: if you set auto increment column for a column, you do not need to set this column when inserting data. It will be auto increment by default (there can only be one auto increment column in the table). Note: 1. For auto increment column, it must be index (including primary key) 2. For auto increment, you can set step size and starting value
     Primary key, a special unique index, is not allowed to have empty values. If the primary key uses a single column, its value must be unique. If it is multiple columns, its combination must be unique.
  • Delete table

    drop table  Table name
  • Empty table contents

    delete from Table name
    truncate table Table name
  • Modify table contents

    Add column:   
    alter table Table name add Column name type
     Delete column:   
    alter table Table name drop column Listing
     Modify column:
    alter table Table name modify column Column name type;  -- type
    alter table Table name change Original column name new column name type; -- Column name, type
     Add primary key:
    alter table Table name add primary key(Listing);
    Delete primary key:
    alter table Table name drop primary key;
    alter table Table name  modify  Listing int, drop primary key;
    Add foreign key: 
    alter table From table add constraint Foreign key name (such as: FK_From table_(main table) foreign key From table(Foreign key field) references Main table(Primary key field);
    To delete a foreign key: 
    alter table Table name drop foreign key Foreign key name
     Modify default:
    ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
    Delete default:

Operation of database table contents (addition, deletion, modification and query)

  • increase

    nsert into surface (Listing,Listing...) values (value,value,...)
    insert into surface (Listing,Listing...) values (value,value,...),(value,value,value...)
    insert into surface (Listing,Listing...) select (Listing,Listing...) from surface
        insert into tab1(name,email) values('test','test8851@163.com')
  • Delete

    delete from surface   # Delete all data in the table
    delete from surface where id=1 and name='test' # Delete the row with ID =1 and name='test '
  • change

    update surface set name = 'test' where id>1
  • check

    select * from surface
    select * from surface where id > 1
    select nid,name,gender as gg from surface where id > 1
     There are too many conditions to check. I'll list them. As for the combination, it depends on your understanding
    a,Conditional judgment where
    select * from surface where id > 1 and name != 'aylin' and num = 12;
    select * from surface where id between 5 and 16;
    select * from surface where id in (11,22,33)
    select * from surface where id not in (11,22,33)
    select * from surface where id in (select nid from surface)
    b,wildcard like
    select * from surface where name like 'zhang%'  # All (multiple strings) beginning with zhang
    select * from surface where name like 'zhang_'  # All (one character) beginning with zhang
    c,limit limit
    select * from surface limit 5;            - First 5 lines
    select * from surface limit 4,5;          - 5 lines from line 4
    select * from surface limit 5 offset 4    - 5 lines from line 4
    d,sort asc,desc
    select * from surface order by column asc              - Arrange columns from small to large
    select * from surface order by column desc             - Arrange columns from large to small
    select * from surface order by Column 1 desc,Column 2 asc    - Sort from large to small according to column 1. If the same, sort from small to large according to column 2
    e,grouping group by
    select num from surface group by num
    select num,nid from surface group by num,nid
    select num,nid from surface  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from surface group by num,nid
    select num from surface group by num having max(id) > 10
     special: group by Must be where After that, order by before