MySQL master-slave Copy Data Summary

MySQL master-slave Copy Data Summary
1. Replication Principle

As shown in 1.png:

 

 

The first part of this process is that the master records binary logs. Before each Transaction Completes data update, the master will record these changes in the second log. MySQL writes transactions into binary logs in sequence, even if the statements in the transaction are executed in a crossover manner. After the event is written to the binary log, the master notifies the storage engine to submit the transaction.

 

The next step is to copy the binary log of the master to its own relay log. First, slave starts a working thread-I/O thread. The I/O thread opens a normal connection on the master and starts the binlog dump process. The Binlog dump process reads the event from the binary log of the master. If it has already followed the master, it will sleep and wait for the master to generate a new event. The I/O thread writes these events to the relay log.

 

SQLslave thread (SQL slave thread) processes the last step of the process. The SQL thread reads the event from the relay log, replays the event, and updates the slave data so that it is consistent with the data in the master. As long as the thread is consistent with the I/O thread, the relay log is usually located in the OS cache, so the overhead of the relay log is very small.

 

In addition, there is also a working thread in the master: Like other MySQL connections, opening a connection in the master will also enable the master to start a thread. There is an important limitation in the replication process-replication is serialized on the slave, that is, the parallel update operations on the master cannot be performed in parallel on the slave.

 

2. mysql master-slave synchronization Application Scenario

(1): Data Distribution

(2): Server Load balancer

(3): Backup

(4): high availability and fault tolerance

 

3. Requirements for building a mysql master-slave Environment

Master/Slave systems must be consistent: including database versions, operating system versions, disk I/O disk capacity, and network bandwidth.

[Root @ data02 ~] # Cat/etc/redhat-release

CentOS release 6.2 (Final)

[Root @ data02 ~] #

	Master database master	Slave
OS version	CentOS release 6.2 (Final)	CentOS release 6.2 (Final)
Database Version	5.6.12-log	5.6.12-log
Disk capacity	50 GB	30G
Host IP Address	192.168.52.129	192.168.52.130
Port	3306	3306
Memory	1G	1G
Server Type	Virtual Machine	Virtual Machine

 

 

4. Start to build mysql master-slave replication 4.1 create a replication account

 

Grant replication slave, RELOAD, super on *. * TO repl @ '192. 168.52.130 'identified BY 'repl _ 100 ';

Create a replication account and only allow access from 192.168.52.130 to log on to the master database for binary log transmission and synchronization. PS: if the new and old password algorithms of mysql are different, you can set password for 'backup '@ '10. 100.0.200' = old_password ('123 '))

 

4.2 manual data synchronization

Because data already exists in the master database at the time of building, you must first manually synchronize the existing data in the master database to the slave database. During the building process, you are prohibited from performing any ddl, dml, and other data operations on the database from the master database.

Here, you can use mysqldump or xtrabackup to export the data in the master database:

(4.2.1): xtrabackup Mode

Back up data on 192.168.52.129 in the master database. Run the backup command to add the -- safe-slave-backup parameter:

Innobackupex -- user = backup -- password = "123456" -- host = 192.168.52.129 -- socket =/tmp/mysql. sock -- defaults-file =/etc/my. cnf/data/backups/mysql/repl/backup_slave -- parallel = 3 -- safe-slave-backup -- no-timestamp

To view the binary information of the master database in the backup directory/data/backups/mysql/repl/backup_slave, perform data synchronization based on the binary information, as shown below:

[Root @ data01 test] # cd/data/backups/mysql/repl/backup_slave

[Root @ data01 backup_slave] # more xtrabackup_binlog_info

Mysql-bin.000147 120

[Root @ data01 backup_slave] #

Compress the backup file and transfer it to the slave database 192.168.52.130:

Tar-zcvf backup_slave.tar.gz backup_slave/

Scp backup_slave.tar.gz192.168.52.130:/tmp/

(4.2.2) mysqldump Method

Back up data on the master database 192.168.52.129

/Usr/local/mysql/bin/mysqldump-ubackup -- password = 123456 -- host = 192.168.52.129 -- single-transaction -- flush-logs -- master-data = 2 -- add-drop-table -- create-option -- quick -- extended-insert = false -- set-charset -- disable-keys-A>/tmp/alldb. SQL

Compress the backup file and transfer it to the slave database 192.168.52.130.

Gzip/tmp/alldb. SQL

Scp/tmp/alldb. SQL .gz 192.168.52.130:/tmp/

4.3 configure the master database (192.168.52.129)

Vim/etc/my. cnf

Server-id = 129 # Server ID

Log-bin =/home/data/mysql/binlog/mysql-bin

Binlog-ignore-db = mysql # No sync databases

Binlog-ignore-db = test # No sync databases

Binlog-ignore-db = information_schema # No sync databases

Binlog-ignore-db = performance_schema

Binlog-do-db = user_db

 

Save and exit. Restart the mysql master database. You can view the status of the master database as follows:

mysql> show master status;+------------------+----------+--------------+--------------------------------------------------+-------------------+| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+--------------------------------------------------+-------------------+| mysql-bin.000151 | 120 | user_db |mysql,test,information_schema,performance_schema | |+------------------+----------+--------------+--------------------------------------------------+-------------------+1 row in set (0.00 sec)mysql>mysql> show master status\G;*************************** 1. row***************************File: mysql-bin.000151Position: 120Binlog_Do_DB: user_dbBinlog_Ignore_DB:mysql,test,information_schema,performance_schemaExecuted_Gtid_Set:1 row in set (0.00 sec)ERROR:No query specifiedmysql>

4.4 configure the slave database (192.168.52.130)

The Slave configuration is similar to that of the master database, as shown below:

Vim /etc/my.cnf#------------------Master-Slaveconfig-----------------log-slave-updates=1replicate-same-server-id=0server-id=230 # Server IDlog-bin=/home/data/mysql/binlog/mysql-bin.logrelay-log=mysql-relay-binmaster-info-repository=TABLErelay-log-info-repository=TABLEbinlog-ignore-db=mysql # No sync databasesbinlog-ignore-db=test # No sync databasesbinlog-ignore-db=information_schema # No sync databasesbinlog-ignore-db=performance_schemabinlog-do-db=user_dbexpire-logs-days=10max_binlog_size = 10485760

Server_id is required and unique. Slave does not need to enable binary logs, but in some cases, it must be set. For example, if slave is another slave master, bin_log must be set. Here, we enable binary logs and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ).

Relay_log configures the relay log. log_slave_updates indicates that slave writes the replication event into its own binary log (which will be useful later ).

Some people have enabled the slave binary log, but have not set log_slave_updates, and then check whether the slave Data has changed. This is an incorrect configuration. Therefore, use read_only whenever possible, which prevents data changes (except for special threads ). However, read_only is very useful, especially for applications that need to create tables on slave.

Configure and restart the slave Database

mysql> show slave status;Empty set (0.05 sec)mysql>

No records. You need to set some Master/Slave configurations.

4.5 set master-slave connection Replication

Generate the change master statement, and then execute it from the top. Obtain the master information from the backup set:

If xtrabackup is backed up, it is obtained from xtrabackup_binlog_info, as shown below:

[root@data02 tmp]#tar -xvfbackup_slave.tar.gz[root@data02 tmp]#cd backup_slave[root@data02 backup_slave]# morextrabackup_binlog_infomysql-bin.000141 120[root@data02 backup_slave]#

For Mysqldump, obtain the first few columns of the SQL file as follows:

[root@data02 tmp]#cd /tmp/[root@data02 tmp]#gunzip alldb.sql.gz[root@data02 tmp]# more alldb.sql |grep"CHANGE MASTER TO MASTER_LOG_FILE" |grep "MASTER_LOG_POS"|more-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;

Generate the changemaster statement as follows:

Change master to MASTER_HOST = '192. 168.52.129 ',

MASTER_USER = 'repl ',

MASTER_PASSWORD = 'repl _ 1234 ',

MASTER_LOG_FILE = 'mysql-bin.000141 ',

MASTER_LOG_POS = 120;

Error message:

Mysql> change master to MASTER_HOST = '192. 168.52.129 ',

-> MASTER_USER = 'repl ',

-> MASTER_PASSWORD = 'repl _ 1234 ',

-> MASTER_LOG_FILE = 'mysql-bin.000141 ',

-> MASTER_LOG_POS = 120;

ERROR 1794 (HY000): Slave is not configuredor failed to initialize properly. you must at least set -- server-id to enableeither a master or a slave. additional error messages can be found in the MySQLerror log.

Mysql>

The specific cause is unknown. The information found on the Internet: the default engine of MyISAM is used to open these tables in the database, but the engine used to create these tables is INNODB.

However, we can be sure that these tables are newly added in mysql5.6.

Innodb_index_stats,

Innodb_tables_stats,

Slave_master_info,

Slave_relay_log_info,

Slave_worker_info

Solution:

Log on to the database, go to the mysql database, and run the following SQL statement to delete five tables:

Remember, it must be drop table if exists

Drop table if exists innodb_index_stats;

Drop table if exists innodb_table_stats;

Drop table if exists slave_master_info;

Drop table if exists slave_relay_log_info;

Drop table if exists slave_worker_info;

After the execution, you can use show tables to check whether the table data has been reduced. If so, you have succeeded!

[Root @ data02 test] cd/home/data/mysql

[Root @ data02 mysql] # ll *. ibd

-Rw ----. 1 mysql 98304 1? 9 innodb_index_stats.ibd

-Rw ----. 1 mysql 98304 1? 9 innodb_table_stats.ibd

-Rw ----. 1 mysql 98304 1? 9 slave_master_info.ibd

-Rw ----. 1 mysql 98304 1? 9 slave_relay_log_info.ibd

-Rw ----. 1 mysql 98304 1? 9 slave_worker_info.ibd

[Root @ data02 mysql] #

Forcibly delete an ibd file:

[Root @ data02 mysql] # rm-f *. ibd

Restart the database and log on to mysql

Source/usr/test/mysql/share/mysql_system_tables. SQL

Show tables;

It is found that the table has been returned, and the total number of table data is about 28.

 

Then run change master to, OK, as shown below:

Mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.03sec)

 

Mysql> change master TOMASTER_HOST = '192. 168.52.129 ', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl _ 100', MASTER_LOG_FILE = 'mysql-bin.000141', MASTER_LOG_POS = 192;

Query OK, 0 rows affected, 2 warnings (0.07sec)

Mysql>

Start slave

Mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

 

Mysql>

 

4.6 verify master-slave replication status

View the slave status on the slave server:

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.52.129

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000151

Read_Master_Log_Pos: 346

Relay_Log_File: mysql-relay-bin.000018

Relay_Log_Pos: 509

Relay_Master_Log_File: mysql-bin.000151

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Replicate_Do_DB: business_db, user_db, plocc_system

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 346

Relay_Log_Space: 845

Until_Condition: None

Here we mainly look:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Seconds_Behind_Master: 0

IO and SQL threads are both Yes and Seconds_Behind_Master is 0, which means the slave database runs normally.

View on the master server:

Mysql> show full processlist;

+ ---- + ----------------- + -------------------- + ------ + ------------- + ------ + Others + ----------------------- +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ----------------- + -------------------- + ------ + ------------- + ------ + Others + ----------------------- +

| 1 | event_scheduler | localhost | NULL | Daemon | 5874 | Waiting on empty queue | NULL |

| 21 | root | localhost | NULL | Query | 0 | init | show full processlist |

| 24 | repl | 192.168.52.130: 45665 | NULL | Binlog Dump | 88 | Master has sent allbinlog to slave; waiting for binlog to be updated | NULL |

+ ---- + ----------------- + -------------------- + ------ + ------------- + ------ + Others + ----------------------- +

3 rows in set (0.03 sec)

Mysql>

We can see that threads with 192.168.52.130: 45665 are synchronizing binary data.

4.7 add data verification to the master

Go to the master (192.168.52.129) and add the table record:

Mysql> create table master_test select 1as a, 'A' as B;

Query OK, 1 row affected (0.72 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql>

Go to the slave (192.168.52.130) and check whether the table data is synchronized. The data has been synchronized, as shown below:

Mysql> select * fromuser_db.master_test;

+ --- +

| A | B |

+ --- +

| 1 | a |

+ --- +

1 row in set (0.06 sec)

Mysql>

5. How to add a new slave server

If the master has been running for a long time and a new slave server needs to be added, create a new slave. There are several ways to enable the slave to start from another service, for example, copying data from the master, clone from another slave and start from the latest backup. Three things are required for synchronization between Slave and master:

(1) Data snapshots at a certain time point of the master;

(2) The current log file of the master and the byte offset when the snapshot is generated. These two values can be called log file coordinates (log file coordinate), because they determine the location of a binary log, you can use the show master status Command to find the coordinates of the log file;

(3) binary log file of the master node.

You can also use the following methods to create an slave:

(1) cold copy (cold copy)

Stop the master, copy the master files to slave, and restart the master. The disadvantage is obvious.

(2) hot copy (warm copy)

If you only use the MyISAM table, you can use mysqlhotcopy to copy the table, even if the server is running.

If myisam and innodb tables exist, you can use the tar package for hot copy during off-peak hours.

(3) Use mysqldump

Using mysqldump to get a data snapshot can be divided into the following steps:

<3.a> lock table: if you have not locked the table, you should lock the table to prevent other connections from modifying the database. Otherwise, the data you get may be inconsistent. As follows:

Mysql> flush tables with read lock;

<3. B> use mysqldump to create a database dump for replication on another connection:

See section 4.2.1.

<3.c> release the lock on the table.

Mysql> unlock tables;

(4) use xtrabackup

Use xtrabackup to get a data snapshot. For details, see section 4.2.2.

6. How to Implement MSS

When log_slave_updates is set, you can have slave Act as the master of other slave instances. In this case, slave writes the SQL thread-executed events to its own binary log. Then, its slave can get these events and execute them. As shown in 6.png:

7. Copy and filter

Replication filtering allows you to copy only part of the data on the server. There are two replication filtering methods: filter events in binary logs on the master node, and filter events in relay logs on the slave node. As shown in 7.png:

 

8. Common mysql master-slave Topology

The replication architecture has the following basic principles:

(1) Each slave can have only one master;

(2) Each slave can have only one unique server ID;

(3) Each master can have many slave instances;

(4) If you set log_slave_updates, slave can be the master of other slave, thus spreading master updates.

MySQL does not support multi-master replication-that is, one slave can have multiple masters. However, through some simple combinations, we can build a flexible and powerful replication architecture.

9. mysql master-slave synchronization Management

This section describes the basic mysql master-slave management commands:

9.1 stop mysql slave Service

Stop slave IO_THREAD; # STOP the IO Process

Stop slave SQL _THREAD; # STOP the SQL Process

Stop slave; # stop io and SQL Processes

9.2 enable mysql master-slave Synchronization Service

Start slave IO_THREAD; # START the IO Process

Start slave SQL _THREAD; # START the SQL Process

Start slave; # start io and SQL Processes

9.3 reset mysql master-slave Synchronization

Reset slave;

# Used to make the slave server forget its replication location in the master server's binary log, it will delete the master.info and relay-log.info files, as well as all the relay logs, and start a new relay log, you can perform this operation from the top when you do not need the master or slave node. Otherwise, it will be synchronized in the future and may overwrite your database.

9.4 view master-slave synchronization status

Show slave status;

# This command mainly checks the values of Slave_IO_Running, Slave_ SQL _Running, Seconds_Behind_Master, Last_IO_Error, and Last_ SQL _Error to grasp the replication status.

9.5 temporarily skipping MYSQL synchronization errors

# If a friend of mysql encounters a master-slave synchronization error, such as a primary key conflict, I need to temporarily skip this error while ensuring that the data of that row is consistent, then you need to use the SQL _SLAVE_SKIP_COUNTER = n command. n indicates that the next n events are skipped. For example, the operations for skipping an event are as follows:

Stop slave;

Set global SQL _SLAVE_SKIP_COUNTER = 1;

Start slave;

9.6 re-synchronize data from a specified location

# When there is a problem with MASTER-slave synchronization, You need to synchronize data from the next location in the log location, which is equivalent to skipping the error. You can also use the change master command to handle it at this time, you only need to find the corresponding LOG location, for example:

Change master TOMASTER_HOST = '10. 1.1.75', MASTER_USER = 'replicase', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000006 ', MASTER_LOG_POS = 123456;

Start slave;

PS: Avoid this operation in the production environment as much as possible.

10. Precautions for online mysql Master/Slave Maintenance

1. Do not use the SQL _SLAVE_SKIP_COUNTER command in disorder.

After this command is skipped, The Master/Slave Data may be inconsistent. You must first record the specified errors and then check whether the data is consistent, especially the core business data.

2. Use the percona-toolkit tool pt-table-checksum to regularly check whether the data is consistent.

This is something DBAs must do on a regular basis. Why not have a proper tool? In addition, percona-toolkit also provides a solution for Database Inconsistency. You can use pt-table-sync, which does not change the master data. You can also use pt-heartbeat to view slave Server replication lags.

3. Use the replicate-wild-ignore-table option instead of the replicate-do-db or replicate-ignore-db option.

The cause is described above.

4. Adjust the log mode of the master server to mixed.

5. Each table is added with a primary key, which affects Database Synchronization, especially in ROW replication mode.