Mysql Master-Slave Replication Summary

Environment : Centos 6.9,mysql 8.0

Configuration preparation: 1, configure the network for CentOS, connect using Remote Tools.

2, installation of MySQL, the path is not limited. (I use RPM package installation, after installation to start the MSYQL Service, command service mysqld start)

3. The default password after MySQL installation is in/etc/my.cnf log-error the log address that the configuration item points to/var/log/mysqld.log

Master-slave replication

Benefits: Improved multi-node performance, higher security, and increased server load.

Application Scenario: Database backup, read/write separation

Step: First. Determine the primary node master, and the others are slave from the node.

1. Configure the Master node. Create user grant permission; open Binlog Log if Binlog is not turned on;

2. Configure the slave node. Configure the synchronization log; Identify the primary node's IP, port, user, boot from node;

Master node (Master):

　　Modify the MySQL configuration file, vim/etc/my.cnf Configure the value of Server-id, and the slave node cannot be duplicated.

Create a user to create. Create user ' repl ' @ ' 192.168.110.% ' identified by ' repl_123456 ';

Give it replication slave permissions on the primary server. Grant Replication Slave on * * to ' repl ' @ ' 192.168.110.% ';

MySQL Log type:

Error log errors

General Query Log

Slow query Log slow lookup logs (records which queries are slow)

Binary Log binary logfile (1) for incremental backups. 2. master-Slave)

Check to see if binlog:show variable like '%log_bin% ' is turned on;

If not turned on, configure: VIM/ETC/MY.CNF plus server-id=1 (multiple node values cannot be duplicated) and Log_bin=/var/lib/mysql/mysql-bin save to exit the restart MySQL service.

View Binlog log: Show Binlog events in ' filename ' in MySQL; Mysqlbinlog the file name in the system;

Each time you restart MySQL, you will create a new binlog. See which Binlog file is currently: Show Master status;

Show msater logs; (View all log files), reset master (empty all log files)

Binlog Log Recovery data

Mysqlbinlog File name | Mysql-u root-p

From node (Slave):

Modify the MySQL configuration file, vim/etc/my.cnf

Configure server_id (cannot be duplicated with the primary node)

Configure the synchronization log path Relay-log=/var/lib/mysql/relay-bin

Go to MySQL execution

Mysql> change MASTER to-master_host= ' 192.168.110.66 ', master_user= ' repl ', master_password= ' Repl_ 123456 ', master_log_file= ' binlog.000007 ',--master_log_pos=0; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> START SLAVE; Query OK, 0 rows Affected (0.00 sec)

　　show slave status\g; check if the master node is linked, modify the configuration according to the error message inside. Make sure the master Firewall is off and the UUID in the/var/lib/mysql/auto.cnf is not duplicated.

If Slave_io_running:yes is present but Slave_sql_running:no is executed

mysql> stop slave;

　　Mysql> set GLOBAL sql_slave_skip_counter=1;

mysql> start slave;

Configuration is successful!

　　

mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start;

Mysql Master-Slave Replication Summary