1. Mysql master-slave replication principle
1. The master records the operation statement into the binlog log, and then grants the remote connection permission to the slave (the master must enable the binlog binary log function; usually for data security considerations, the slave also enables the binlog function).
2. The slave starts two threads: IO thread and SQL thread. Among them: the IO thread is responsible for reading the binlog content of the master into the relay log; the SQL thread is responsible for reading the binlog content from the relay log and updating it to the slave database, so that the slave data and the master data can be kept agreed.
3. Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server.
4. For Mysql replication, it is best to ensure that the Mysql versions on the master and slave servers are the same (if the version consistency cannot be satisfied, then ensure that the version of the master master node is lower than the version of the slave slave node)
5. The time between master and slave nodes needs to be synchronized
Two, Centos7 install Mysql
1. Check the installation environment
1.1. Check whether mysql has been installed
[root@localhost ~]# rpm -qa | grep mysql #If there is, use the rm -rf command to delete [root@localhost ~]# whereis mysql mysql: /usr/lib64/mysql /usr/share/mysql [root@localhost ~]# rm -rf /usr/lib64/mysql /usr/share/mysql #delete [root@localhost ~]# find / -name mysql /etc/selinux/targeted/active/modules/100/mysql [root@localhost ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql #delete
1.2. Check whether there is a mysql user
If the user and user group already exist, skip this step, if not, create it
[root@localhost ~]# id mysql id: mysql: no such user [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -r -g mysql mysql
1.3. Check whether the library file is installed
[root@localhost ~]# rpm -qa|grep libaio libaio-0.3.109-13.el7.x86_64 #I have already installed it here, if there is no output, you need to install it with yum yum install libaio-devel.x86_64 # yum -y install numactl
2. Download address
https://dev.mysql.com/downloads/mysql/
After the download is complete, use the rz command to upload to the /soft/ directory
3. Installation
[root@localhost local]# tar -xvf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz #decompress [root@localhost local]# mv mysql-8.0.27-linux-glibc2.12-x86_64 mysql-8.0.27 #Rename #Modify the user of all files in the mysql directory to be root and the group to be root [root@localhost local]# chown -R root:root /soft/mysql-8.0.27/ `insert code slice here` #Create a data storage directory to store database data [root@localhost local]# mkdir data #Modify the user and group to which the data storage directory belongs to mysql [root@localhost local]# chown mysql:mysql /soft/mysql-8.0.27/data
4. Initialize the database
Please confirm that /etc/my.cnf does not exist before initialization, otherwise mysql will read the configuration file as initialization by default during initialization.
If the file exists, rename it as follows and keep it as a backup
[root@localhost mysql]# ll /etc/ | grep my.cnf -rw-r--r--. 1 root root 570 Jun 8 2017 my.cnf drwxr-xr-x. 2 root root 31 Dec 20 17:32 my.cnf.d [root@localhost mysql]# mv /etc/my.cnf /etc/my.cnf.bak [root@localhost mysql]# ll /etc/ | grep my.cnf -rw-r--r--. 1 root root 570 Jun 8 2017 my.cnf.bak drwxr-xr-x. 2 root root 31 Dec 20 17:32 my.cnf.d #start initialization [root@localhost mysql]# /soft/mysql-8.0.27/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql-8.0.27 --datadir=/soft/mysql-8.0.27/data
Note: Remember to save the initialization password! ! !
Record BUG!
Starting MySQL... ERROR! The server quit without updating PID file (/soft/mysql-8.0.27/data/mysql.pid).
Solution:
permissions
chmod -R 755 /soft/mysql-8.0.27/data
check process and kill
ps -ef|grep mysqld
kill -9 ****
restart mysql
service mysql start
5. Modify the configuration
#Enter the support-files file in the MySQL installation directory, create the mysql-default.cnf file and add permissions [root@localhost mysql]# cd /soft/mysql-8.0.27/support-files/ [root@localhost support-files]# touch mysql-default.cnf [root@localhost support-files]# chmod 777 mysql-default.cnf #Copy it to /etc/ and rename it to my.cnf [root@localhost support-files]# cp -a mysql-default.cnf /etc/my.cnf vi /etc/my.cnf #Add configuration in my.cnf [mysqld] basedir = /soft/mysql-8.0.27 datadir = /soft/mysql-8.0.27/data socket = /tmp/mysql.sock log-error = /soft/mysql-8.0.27/data/error.log pid-file = /soft/mysql-8.0.27/data/mysql.pid port = 3306 max_allowed_packet=32M #Modify configuration file permissions chmod 644 /etc/my.cnf
6. Set the boot to start automatically
#Copy the mysql service file to the /etc/init.d/ directory and rename it to mysql [root@localhost support-files]# cp -a /soft/mysql-8.0.27/support-files/mysql.server /etc/init.d/mysql #Add executable permissions [root@localhost support-files]# chmod +x /etc/init.d/mysql #registration service chkconfig --add mysql #register chkconfig --list mysql #detection #/etc/ld.so.conf must configure the path, otherwise an error will be reported [root@localhost support-files]# vim /etc/ld.so.conf #Add the lib file of mysql in ld.so.conf /soft/mysql-8.0.27/lib #Configure environment variables [root@localhost support-files]# vim /etc/profile #Add at the end: export PATH=$PATH:/soft/mysql-8.0.27/bin:/soft/mysql-8.0.27/lib #Reload to make the environment variable take effect [root@localhost support-files]# source /etc/profile #start service [root@localhost support-files]# service mysql start
7. Modify the default password
#Login with initial password [root@localhost support-files]# mysql -uroot -p
If you forget the initial password:
https://zhuanlan.zhihu.com/p/458845050
# change Password alter user 'root'@'localhost' identified by 'root';
8. Open remote connection
mysql> use mysql; mysql> update user set host = '%' where user = 'root'; mysql> flush privileges; mysql> GRANT ALL ON *.* TO 'root'@'%'; mysql> flush privileges;
#firewall settings #Temporarily turn off the firewall systemctl stop firewalld #Open the specified port number firewall-cmd --zone=public --add-port=3306/tcp --permanent #remove open ports firewall-cmd --zone=public --remove-port=3309/tcp --permanent #Enable firewall settings to take effect firewall-cmd --reload
3. Data synchronization
We need a master node, which is the master database Mysql (provides the update log for the slave database)
1. The master node opens the binary log
#Master node configuration my #Add the following code in /etc/my.cnf #Enable binary logging log_bin=master_bin #Set the global unique ID of the current node (set by yourself) server-id=140 #Restart the mysql service service mysql start #See the file and location of the binary log to start copying from mysql> show master status;
2. Create a user account with replication permissions in the master node library
create user repluser@'10.70.20.%' identified by 'password'; mysql> grant replication slave on *.* to repluser@'10.70.20.%';
3. Slave node configuration
#Set a globally unique ID number for the current node server_id=98 #Enable binary logging log-bin=slave_log #Set read-only from the database (not valid for root users) read_only=ON #The file path of the relay log relay_log=relay-log #Default value hostname-relay-bin.index relay_log_index=relay-log.index
2. Use a user account with replication permission to connect to the master server from the node and start the replication thread
mysql> change master to master_host='master node ip',master_port=3306,master_user='rep_username',master_password='password',master_log_file='master_bin.000001',MASTER_LOG_POS= 1827,get_master_public_key=140; #rep_username: The username of the new user just registered by the host mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G;
The following figure shows success