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

The principle of Mysql database replication:

Overall, there are three steps to replication:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and changes the data that reflects itself.

The following figure describes the replication process:

The first part of the process is that master records binary logs. Master records these changes in the binary log before each transaction updates the data. 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 Iswap O thread writes these events to the relay log.

SQL slave 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 slave's data to match 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.

Specific configuration

Environment

MySQL-master1:10.6.1.210

MySQL-master2:10.6.1.211

OS version: CentOS 6.6

MySQL version: 5.1.73

I. MySQL master-master configuration

1.1.Modification of MySQL configuration file

If you want to enable the binlog log feature for both MySQL, you can enable it in the MySQL-master1 configuration file / etc/my.cnf.

Add to the [MySQLd] paragraph

User = mysql

Log-bin=mysql-bin

Binlog_format=mixed

Relay-log=relay-bin

Server-id = 1

Binlog-do-db=test

Binlog-ignore-db=mysql

Replicate-do-db=test

Replicate-ignore-db=mysql

Log-slave-updates=1 (log-slave-updates is also OK)

Slave-skip-errors=all

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

In the MySQL-master2 configuration file / etc/my.cnf

Add to the [MySQLd] paragraph

User = mysql

Log-bin=mysql-bin

Binlog_format=mixed

Relay-log=relay-bin

Server-id=10

Binlog-do-db=test

Binlog-ignore-db=mysql

Replicate-do-db=test

Replicate-ignore-db=mysql

Log-slave-updates=1 (log-slave-updates is also OK)

Slave-skip-errors=all

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=2

Save the two configuration files and restart the mysql server respectively

Explain several important common options:

Server_id

The value must be a positive integer value between 2 and 232-1. The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different.

Binlog-do-db=database

Is the database to log

Synchronize multiple database repeat setting options binlog-do-db=test and replicate-do-db=test

For example

Binlog-do-db=test1

Replicate-do-db=test1

Binlog-do-db=test2

Replicate-do-db=test2

Binlog-ignore-db

Do not record the database name of the log, separate multiple databases with commas (,)

Log-slave-updates=1

This parameter is used to configure whether updates from the server are written to the binary log. This option is not turned on by default, but if the slave server B is the slave server of server An and also serves as the master server of server C, then you need to develop this option so that its slave server C can get its binary log for synchronization.

Sync_binlog=1

This parameter directly affects the performance and integrity of mysql. After n transaction commits, Mysql will execute a disk synchronization instruction such as fsync, and the gay file system flushes the Binlog file cache to disk. The default setting in Mysql is sync_binlog=0, that is, no mandatory disk refresh instructions are made, which is the best performance, but also the greatest risk. Once the system Crash, all Binlog information in the file system cache is lost

Auto_increment_increment=2

Auto_increment_offset=1

The values generated by the auto_increment field are: 1, 3, 5, 7,... Waiting for odd ID.

Auto_increment_increment = 2

Auto_increment_offset = 2

The values generated by the auto_increment field are: 2, 4, 6, 8,... Wait for the even number ID.

This avoids conflicts between the values of the self-growing fields when two servers update at the same time.

In addition:

Master-connect-retry

Master-connect-retry is used to set the interval between retries when a connection to the master server is lost. The default is 60 seconds.

Read-only

Read-only is used to restrict the update operation of the slave database by ordinary users to ensure the security of the slave database, but if you are a super user, you can still update the slave database.

Slave-skip-errors

In the process of replication, due to various reasons, the slave server may encounter an error in executing SQL in BINLOG. By default, the server will stop the replication process and no longer synchronize until the user handles it.

The function of Slave-skip-errors is to define the error number that can be skipped automatically from the server during replication. When a defined error number is encountered in the replication process, it can be skipped automatically and execute the following SQL statement directly.

Skip-slave-start

Indicates that the synchronization thread is not started when starting from the mysql server, which means starting the synchronization thread manually after starting the slave server

1.2 make sure that the data of the two databases are consistent before synchronization.

If there is data in the main database. The database locks the table operation so that the data is not written again.

Mysql > FLUSH TABLES WITH READ LOCK

Back up the database with the command mysqldump.

Execute on the master server

Shell > mysqldump-uroot-p123456 test > test.sql

Look at the status of the main database mysql > show master status

Record the values of File and Position entries

Note: the master server is not locked, and the master server binary log position value recorded here may be greater than the value when doing mysqldump, which will cause the slave server to lose updates during this period. If you can guarantee that updates to create new tables will not occur on the master server during this period, the loss will not have much impact; otherwise, it will cause the replication thread from the server to fail, and the master server must be locked while mysqldump is being done.

Add options from the my.cnf file in the server

Skip-slave-start

Indicates that the synchronization thread is not started when starting from the mysql server, which means that after starting the slave server, start the synchronization thread manually and run "start slave" under the mysql > prompt

After saving my.cnf

Execution

Shell > mysqladmin-uroot-p123456 create test

Shell > mysql-uroot-p123456 test start slave

Unlock the primary database mysql > UNLOCK TABLES

1.3. Set 10.6.1.210 to the primary server of 10.6.1.211

Create a new authorized user on 10.6.1.210

MySQL > grant replicationclient,replication slave on *. * to 'repluser'@'10.6.1.211' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

MySQL > flush privileges

Query OK, 0 rows affected (0.00 sec)

View the bin file and time point on the Master database. After logging in to the mysql of the Master server, execute:

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000015 | 2474 | test |

+-+

1 row in set (0.00 sec)

Here, the bin file is mysql-bin.000015 and the node is 2474.

Set 10.6.1.210 as your primary server at 10.6.1.211

MySQL > change master to master_host='10.6.1.210',master_user=' repluser', master_password='123456',master_log_file='MySQL-bin.000015',master_log_pos=2474

Query OK, 0 rows affected (0.05 sec)

The red marked part is the two values of file and postion in the result of show master status; command in 10.6.1.210 host.

MySQL > start slave

Query OK, 0 rows affected (0.00 sec)

MySQL > show slave status/G

...

...

Whether Slave_IO_Running:Yes # IO thread is running

Whether Slave_SQL_Running:Yes # SQL thread is running

...

1.4. Set 10.6.1.211 to the primary server of 10.6.1.210

Create a new authorized user on 10.6.1.211

MySQL > grant replicationclient,replication slave on *. * to 'repluser'@'10.6.1.210' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

MySQL > flush privileges

Query OK, 0 rows affected (0.00 sec)

View the bin file and time point on the Master database. After logging in to the mysql of the Master server, execute:

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 1476 | test |

+-+

1 row in set (0.00 sec)

Here, the bin file is mysql-bin.000004 and the node is 1476.

Set 10.6.1.211 as your primary server at 10.6.1.210

MySQL > change master to master_host='10.6.1.211',master_user=' repluser', master_password='123456',master_log_file='MySQL-bin.000004',master_log_pos=1476

Query OK, 0 rows affected (0.05 sec)

MySQL > start slave

Query OK, 0 rows affected (0.00 sec)

MySQL >

MySQL > show slave status/G

...

...

Whether Slave_IO_Running:Yes # IO thread is running

Whether Slave_SQL_Running:Yes # SQL thread is running

...

1.5. Other commands

1.5.1. Check the progress of replication

Need to run on the main library

Mysql > show processlist\ G

1.5.2. Related commands on the primary server:

Show processlist

Show master status

Show slave hosts

Show {master | binary} logs

Show binlog events

Purge {master | binary} logs to 'log_name'

Purge {master | binary} logs before 'date'

Reset master (older version of flush master)

Set sql_log_bin= {0 | 1}

1.5.3. From the relevant commands on the server:

Slave start

Slave stop

Slave stop IO_THREAD / / this thread writes the log of the master segment locally

Slave start IO_THREAD

Slave stop SQL_THREAD / / this thread applies the log written locally to the database

Slave start SQL_THREAD

Reset slave

Set global sql_slave_skip_counter

Load data from master

Show slave status (SUPER,REPLICATION CLIENT)

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= / / dynamically change master information

PURGE MASTER [before 'date'] deletes logs that have been synchronized on the master side

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.