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

This article mainly introduces "what are the implementation steps of mysql master-slave replication". In daily operation, I believe that many people have doubts about the implementation steps of mysql master-slave replication. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what are the implementation steps of mysql master-slave replication?" Next, please follow the editor to study!

Catalogue

Mysql master-slave replication

The way of mysql master-slave replication

The principle of mysql master-slave replication

The concrete implementation of the master-slave configuration of mysql

1. Master configuration

2. Slave configuration

Mysql master-slave replication

Originally, I was doing sharding, but after I finished it, I found that the master-slave replication of the database had not been done yet, so I made some records after the configuration:

The way of mysql master-slave replication

There are two ways of mysql master-slave replication: log-based (binlog) and GTID-based (global thing identifier). This blog uses log-based (binlog) replication.

The principle of mysql master-slave replication

1. Master records the data to the binary log, which is the file specified by the log-bin of the mysql configuration file my.cnf. These records are called binary log events.

2. There are two threads in Slave, one is the I _ big O thread and the other is the SQL thread. The Master O thread requests the resulting binlog log to be written to the relay log (Relay Log) file.

3. The SQL thread will read the log in the relay log file and parse it into specific operations to achieve the consistency of the master-slave operation and the final data consistency.

The specific implementation of the master-slave configuration of mysql 1. Master configuration

(1) modify the my.cnf configuration file of mysql. The trick here is that the version of mysql I use does not have a my.cnf configuration file, not even my-default.cnf. Generally speaking, my.cnf is in the / etc directory and my-default.cnf is in / usr/local/mysql/support-files. Finally, I copied a my.cnf file online and put it in / etc (friends can find it on the Internet, there are many).

(2) the configuration that needs to be modified in my.cnf is mainly to enable log function, set server_id to ensure its uniqueness (not the same as slave database), and filter the database to be backed up.

Bash-3.2# vim / etc/my.cnf# sets server_id, which cannot be repeated. Generally, you can set IPserver_id=81# backup filtering as the main database: output binlogbinlog-do-db=demo_ds_master# backup filtering for databases that need to be backed up: for databases that do not need to be backed up, do not output binlog-ignore-db=mysql# to open binary logs. Record the master database data to the log file at the beginning of mysql-bin (customizable) log-bin=mysql-bin# configures the memory of each session, the cache binlog _ cache_size=1M# master-slave replication format used to store binary logs during transactions (mixed,statement,row default is statement) the number of days for binlog_format=mixed# binary logs to automatically delete / expire, default is 0 Do not delete expire_logs_days=7# automatically to skip all errors or specified error types encountered in master-slave replication to avoid terminal # 1062: primary key conflict (specific parameters can be queried online) slave_skip_errors=1062

(3) start / restart the Master database service, log in to the database, create data synchronization users, and authorize

# authorize this machine to back up the data of the master database mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'root'@'192.168.31.60' identified by' password';# and then refresh permissions mysql > flush privileges;# to view configured master database information mysql > show master status

(4) backup the data of the master database to ensure the data consistency between the master and slave databases.

# temporarily lock the table, set read-only permissions mysql > flush tables with read lock;# backup database bash-3.2# mysqldump-uroot-p-- add-drop-table demo_ds_master_1 > / tmp/demo_ds_master_1.sql;# unlock table mysql > unlock table;2, Slave configuration

(1) modify the my.cnf configuration file of the slave database mysql, and add the relay log configuration of the slave database.

[root@develop etc] # vim my.cnf# sets server_id, which cannot be repeated. Here, the slave database relay log is enabled from the IPserver-id=60# of the library, and the binlog of the master database is written to relay log. The relay log starts with hehe-mysql-relay-bin (customizable) relay_log=hehe-mysql-relay-bin# sets the slave database as read-only permission to prevent inconsistent read_only=1 of master-slave data.

(2) if the slave library needs to be the master library of another library, you need to add the configuration information on Master and log_slave_updates=1 to record the binlog of Master in your own binlog log.

(3) restart the slave database and synchronize the master database

# run the backup database of the primary database [root@develop etc] # mysql-uroot-p demo_ds_master_1

Change master to master_host='192.168.31.80', master_user='root',master_password='password',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=154;# enable Master / Slave synchronization mysql > start slave;# to view Master / Slave synchronization status mysql > show slave status\ show slave status # View Master / Slave Thread status mysql > show processlist\ G

The picture is shown as follows, indicating that master-slave replication has been configured. The master database can automatically copy the master database data from the master library by inserting data below.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

At this point, the study on "what are the implementation steps of mysql master-slave replication" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.