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

1, the principle of replication

Official reference document: http://dev.mysql.com/doc/refman/5.6/en/replication.html

Blog address 1: http://blog.csdn.net/mchdba/article/details/11354771

Blog address 2: http://blog.csdn.net/mchdba/article/details/8717513

As shown in the following figure 1.png:

The first part of the process is that master records binary logs. Before each transaction updates the data, master records these changes in the second log. MySQL writes transactions serially to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is for slave to copy master's binary log to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

SQLslave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in slave to make it consistent with the data in master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small.

In addition, there is a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. There is an important limitation to the replication process-replication is serialized on slave, which means that parallel update operations on master cannot operate in parallel on slave.

2application scenario of master-slave synchronization in MySQL

(1) data distribution

(2): load balancing

(3): backup

(4): high availability and fault tolerance

3. Set up the environmental requirements of mysql master and slave.

Master-slave systems should be consistent: including database version, operating system version, disk IO disk capacity, network bandwidth, etc.

[root@data02 ~] # cat / etc/redhat-release

CentOS release 6.2 (Final)

[root@data02 ~] #

Main library master

From library slave

OS system version

CentOS release 6.2 (Final)

CentOS release 6.2 (Final)

Database version

5.6.12-log

5.6.12-log

Disk capacity

50G

30G

Host ip address

192.168.52.129

192.168.52.130

Port

3306

3306

Memory

1G

1G

Server Typ

Virtual machine

Virtual machine

4. Start to set up mysql master-slave replication 4.1 and establish replication account

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *. * TO repl@'192.168.52.130' IDENTIFIED BY 'repl_1234'

Set up a copy account and only allow access to the login main library from 192.168.52.130 for binary log transfer synchronization. PS: if the new and old password algorithms of the mysql version are different, you can set set password for 'backup'@'10.100.0.200'=old_password (' 1234'))

4.2 manually synchronize data

Because when you start to build, there is already data on the master database, so it is necessary to manually synchronously migrate the data that already exists in the master database to the slave database. During the construction process, any ddl, dml and other data operations on the database on the master database and slave database are prohibited.

Here you can use mysqldump or xtrabackup to export the data above the main library:

(4.2.1): xtrabackup mode

Backup the data above 192.168.52.129 on the main database, 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

Go to the backup directory / data/backups/mysql/repl/backup_slave to check the binary information of the main database during backup. You need to synchronize data according to this 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 slave library 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 mode

Make a data backup based on the main database on 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 library 192.168.52.130.

Gzip / tmp/alldb.sql

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

4.3 configure the master library (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 library, and check the status of the master library, 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.000151

Position: 120

Binlog_Do_DB: user_db

Binlog_Ignore_DB:mysql,test,information_schema,performance_schema

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

4.4 configure slave slave library (192.168.52.130)

The configuration of Slave is similar to that of the master library, as follows:

Vim / etc/my.cnf

#-Master-Slaveconfig-

Log-slave-updates=1

Replicate-same-server-id=0

Server-id=230 # Server ID

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

Relay-log=mysql-relay-bin

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

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

Expire-logs-days=10

Max_binlog_size = 10485760

Server_id is necessary and unique. It is not necessary for slave to turn on binary logging, but in some cases, it must be set, for example, if slave is the master of another slave, bin_log must be set. Here, we turn on the binary log and display the name (the default name is hostname, but there will be a problem if the hostname changes).

Relay_log configures relay logs, and log_slave_updates indicates that slave writes replication events to its own binary log (you'll see its usefulness later).

Some people open the binary log of slave, but do not set log_slave_updates, and then check to see if the data of slave has changed, which is a misconfiguration. Therefore, try to use read_only, which prevents data from being changed (except for special threads). However, read_only is not very useful, especially for applications that need to create tables on slave.

Configure to play, restart the slave database

Mysql > show slave status

Empty set (0.05sec)

Mysql >

There is no record, you need to set up some master-slave configuration.

4.5 set up master-slave connection replication

Generate the CHANGE MASTER statement, then execute the master information on the from, and get it from the backup collection:

For xtrabackup backup, obtain it 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_info

Mysql-bin.000141 120

[root@data02 backup_slave] #

For Mysqldump, get it from 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

There is an 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 reason is currently unknown, the information found on the Internet: the default engine for the database to open these tables is MyISAM, but the engine for these tables is INNODB when they are created.

But to be sure, these tables are new in mysql5.6.

Innodb_index_stats

Innodb_tables_stats

Slave_master_info

Slave_relay_log_info

Slave_worker_info

Solution:

Log in to the database, enter the mysql library, and execute the following SQL to delete 5 tables

Remember, it has to 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 execution, you can check with show tables to see if the data in the table has been reduced compared with that before deletion. If so, you have succeeded!

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

[root@data02 mysql] # ll * .ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 innodb_index_stats.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 innodb_table_stats.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_master_info.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_relay_log_info.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_worker_info.ibd

[root@data02 mysql] #

Forcibly delete the ibd file:

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

Restart the database and log in to mysql

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

Show tables

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

Then execute change master to,OK and get it done, 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_1234',MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120

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

The main points here are:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Both IO and SQL threads are Yes, and a Seconds_Behind_Master of 0 means that the slave library is running normally.

View on the master server:

Mysql > show full processlist

+-+- -- +

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

+-+- -- +

| | 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 Binlog Dump 45665 | NULL | Binlog Dump | 88 | Master has sent allbinlog to slave; waiting for binlog to be updated | NULL |

+-+- -- +

3 rows in set (0.03 sec)

Mysql >

See that the thread of 192.168.52.130 45665 is synchronizing binary data.

4.7 master add data validation

Go to master (192.168.52.129) to add table records:

Mysql > create table master_test select 1as a'as b

Query OK, 1 row affected (0.72 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql >

Go to slave (192.168.52.130) to check whether the table data has been synchronized, and see that 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 summary

If master is running for a long time and you need to add a new slave server, then build a new slave, and there are several ways to make slave start with another service, for example, copy data from master, clone from another slave, and start a slave from the most recent backup. When Slave synchronizes with master, you need three things:

(1) data snapshot at a certain time in master

(2) the current log file of master and the byte offset when the snapshot was generated. These two values can be called log file coordinates (log file coordinate) because they determine the location of a binary log, and you can use the SHOW MASTER STATUS command to find the log file coordinates

(3) binary log file of master.

You can also clone a slave in the following ways:

(1) Cold copy (cold copy)

Stop master, copy the master file to slave;, and restart master. The disadvantages are obvious.

(2) Hot copy (warm copy)

If you only use MyISAM tables, you can use mysqlhotcopy copies, even if the server is running.

If you have myisam and innodb tables, you can use tar packages to make hot copies during business troughs.

(3) use mysqldump

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

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 can be inconsistent. As follows:

Mysql > FLUSH TABLES WITH READ LOCK

Create a dump of the database you want to replicate with mysqldump on another connection:

See section 4.2.1

Release the lock on the table.

Mysql > UNLOCK TABLES

(4) use xtrabackup

Use xtrabackup to get a snapshot of the data, see section 4.2.2

6. How to realize MSS

When setting up log_slave_updates, you can ask slave to play the master of other slave. At this point, slave writes the events executed by the SQL thread into its own binary log (binary log), and then its slave can get these events and execute it. As shown in the following figure 6.png:

7, copy filtering

Replication filtering allows you to replicate only part of the data in the server. There are two types of replication filtering: filtering events in binary logs on master and events in relay logs on slave. As shown in the following figure 7.png:

8, commonly used mysql master-slave topology

The replicated architecture has the following basic principles:

(1) there can be only one master per slave

(2) each slave can have only one unique server ID.

(3) each master can have many slave.

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

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

Management of synchronization between master and slave in 9meme MySQL

Introduce the basic mysql master-slave management operation commands:

9.1 stop the 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; # starts IO process and SQL process

9.3 reset mysql master-slave synchronization

RESET SLAVE

# used to make the slave server forget its replication location in the binary log of the master server, it deletes the master.info and relay-log.info files, as well as all relay logs, and starts a new relay log, which you can do on the slave when you don't need a master slave. Otherwise, it will be synchronized later, and your database may be overwritten.

9.4 View master-slave synchronization status

SHOW SLAVE STATUS

# this command mainly looks at the values of Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master, Last_IO_Error, and Last_SQL_Error to grasp the status of replication.

9.5 temporary skip MYSQL synchronization error

# my friend mysql often encounters an error in master-slave synchronization, such as a primary key conflict, so I need to temporarily skip this error while ensuring the consistency of that row of data. Then I need to use the SQL_SLAVE_SKIP_COUNTER = n command, where n means to skip the next n events. For example, I skip an event as follows:

STOP SLAVE

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

START SLAVE

9.6 resynchronize data from a specified location

# sometimes when there is a problem with master-slave synchronization, you need to synchronize from the next location of the log location, which is equivalent to skipping that error. You can also use the CHANGE MASTER command to deal with it, as long as you find the corresponding LOG location, for example:

CHANGE MASTER TOMASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=106

START SLAVE

PS: this operation can be avoided as much as possible in a production environment.

10. Matters needing attention in online maintenance of mysql master and slave

1. Do not misuse the SQL_SLAVE_SKIP_COUNTER command.

Skipping this command is likely to lead to inconsistency between your master and slave data. Be sure to record the specified error first, and then check whether the data is consistent, especially the core business data.

two。 Check whether the data is consistent with the percona-toolkit tool pt-table-checksum on a regular basis.

This is something that DBA must do on a regular basis, hehe, why not have the right tools? In addition, percona-toolkit also provides a solution to database inconsistency, you can use pt-table-sync, this tool does not change the master data. You can also use pt-heartbeat to view replication backwardness from the server.

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

The reason has been explained above.

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

5. A primary key is added to each table, which has an impact on database synchronization, especially in ROW replication mode.

6. Avoid batch update operations in master, so as to avoid slave master-slave delay.

-

Original blog address: http://blog.itpub.net/26230597/viewspace-1478126/

Original author: yellow fir (mchdba)

-

Refer to the article address:

Http://www.open-open.com/lib/view/open1373874692544.html

Http://blog.chinaunix.net/uid-20639775-id-3254611.html

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.