Centos7.4 how to compile and install mysql using cmake

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

Basic overview

Deployment environment

system	address
CentOS Linux release 7.4.1708	192.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)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|: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)... 184.31.28.242
Connecting to cdn.mysql.com (cdn.mysql.com)|184.31.28.242|: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 \
> -DWITH_XTRADB_STORAGE_ENGINE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_PARTITION_STORAGE_ENGINE=1 \
all \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \
> -DWITH_READLINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_EXTRA_CHARSETS=1 \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DEXTRA_CHARSETS=all \
> -DWITH_BIG_TABLES=1 \
> -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:   
Data::Dumper

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
 auto_increment_offset=1    	
 auto_increment_increment=2

 [mysqld_safe]  									mysql Service security startup configuration
 log-error=/var/log/mysqld.log  
 pid-file=/var/run/mysqld/mysqld.pid   
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.
innodb_buffer_pool_size
													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);
[mysqld]
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.
[mysqldump]
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 192.168.10.10 host to access the local ISP library through huang user and admin123 password;

mysql> grant all on ISP.* to huang@'192.168.10.10' 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*
skip-grant-tables
[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> 
mysql> update user set password = Password('hebao') where user = 'root';										   5,Change Password
mysql> flush privileges;						  6,Refresh permissions
mysql> quit;

summary

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'@'192.168.10.10' identified by '123';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> drop user 'hbs'@'192.168.10.10';
 Query OK, 0 rows affected (0.00 sec)
 
 
 mysql> rename user 'hbs'@'192.168.10.10' to 'liuaoni'@'192.168.10.13';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> 
 mysql> set password for 'liuaoni'@'192.168.10.13'=Password('!23');
 Query OK, 0 rows affected (0.00 sec)
 
 mysql>

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
)ENGINE=InnoDB DEFAULT CHARSET=utf8
 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`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Note:
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:
ALTER TABLE testalter_tbl ALTER i DROP 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
 Example:
    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

Programmer Think