Shulou(Shulou.com)06/01 Report--

This article mainly introduces MySQL 5.7 master-slave replication in detail, hoping to give you some additional and updated knowledge, if there are other issues to understand, you can continue to pay attention to my updated article in the industry information.

MySQL 5.7 Master-slave replication

https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html

GTID(Global Transaction ID) is the number for a committed transaction and is a globally unique number. GTID is actually composed of UUID+TID. where UUID is the unique identifier of a MySQL instance. TID represents the number of transactions committed on the instance and increases monotonically with transaction commit.

Set gtid mode in the mysqld tab of my.cnf. After setting, restart mysql service to take effect:

[mysqld]

gtid_mode=ON

log-slave-updates=ON

enforce-gtid-consistency=ON

mysql> change master to

master_host='master library IP',

master_port= master port,

master_user='repl',

master_password='repl password',

master_auto_position=1;

mysql> start slave;

PS: There is a difference here. Previously, non-gtid used master_log_file ='mysql-bin. 00009', master_log_pos=154; while gtid used master_auto_positon=1; to automatically synchronize binlog of the master library.

View copy status from library, yes double YES, and Seconds_Behind_Master: 0:

Tell the slave to use the master with GTID based transactions as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning. Issue a CHANGE MASTER TO statement on the slave, including the MASTER_AUTO_POSITION option in the statement to tell the slave that the master's transactions are identified by GTIDs.

View backup information

1. show slave hosts;

SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND = 'Binlog Dump';

view sync status

1.Show slave status\GSELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

Build cascade backup library

Log_slave_updates=1 is required to make the backup database generate binlog backup database and establish new database through backup. The new database is synchronized with the backup database.

The new database was changed to synchronize with the master database

reset slave;

change master to

master_host='master library IP',

master_port= master port,

master_user='repl',

master_password='repl password',

master_auto_position=1;

su - mysql

2. Login to an instance of the default (3306) port

source .profile

mysql -uroot -p -h227.0.0.1 -P3306

3. Example of logging in to port 3307

source .p3307

mysql -uroot -p -h227.0.0.1 -P3307

Knowledge Points:

1. The.mysql_history file records the commands executed by mysql users, which have security risks and need to be soft connected. lns -l /dev/null $HOME/.mysql_history

2, mysql how to build semi-synchronous and asynchronous standby library.

3, mysql environment installation. Note to modify the owner and VIP configuration of the/relay_log folder.

After reading the above detailed explanation of MySQL 5.7 master-slave replication, I hope to bring some help to everyone in practical application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer your questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.