MySQL Master-slave replication

First, the principle of master-slave replication:

The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. Simply put: The IO thread from the server gets the binary log from the primary server, saves it locally as a trunk log, and then executes the contents of the relay log from the top of the SQL thread to keep the library and the main library consistent.

A.master will change the record to binary log (these are called binary log events, binary logs event);

The b.slave copies the binary log events of master to its trunk log (relay log);

C.slave redo the events in the trunk log and change the data to reflect its own.

1, slave above the IO process connected to master, and request from the specified log file at the specified location (or from the beginning of the log) after the contents of the log;

2, Master received from the slave IO process request, through the responsible copy of the IO process according to the request information read the log specified location after the log information, returned to the slave IO process. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;

3, after the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. In order to be able to clearly tell the master "where to start the log content from Bin-log, and then give slave" in the next reading;

4, slave's SQL process detects the new content in the Relay-log, will immediately parse the contents of Relay-log as the real execution of the master side of the executable content, and execute on its own.

5, MySQL replication is an asynchronous replication process, from master to slave. The entire replication process between master and slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the slave side, and another thread (IO thread) on the master side.

6, the key point: responsible for the main, from the server to transfer a variety of changes in the media is the primary server's binary change log, which records the need to transfer to the server from the various modification actions. Therefore, the primary server must activate the binary logging feature. The slave must have sufficient permission to connect to the primary server and request that the primary server transfer the binary change log to it.

ii. Description of the environment :

Master's ip:192.168.44.151

Slave's ip:192.168.44.152

Third, the primary server configuration:

1, modify the configuration file, add the following content

Vi/etc/my.cnf

Server-id=1

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2. Restart the service to make the configuration file effective

/etc/init.d/mysqld restart

3. Log in to the master server

Mysql-uroot-p

4. Create synchronization user Bob, authorize sync permissions to 192.168.44.152 from server

mysql> create user ' Bob ' @ ' 192.168.44.152 ' identified by ' 123456 ';

Mysql>grant replication Slave on * * to ' bob ' @ ' 192.168.44.152 ' identified by ' 123456 ';

5. View the primary server status

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000002 |              106 | | MySQL |

+------------------+----------+--------------+------------------+

Four, from the server configuration:

1, modify the configuration file, add the following content

Vi/etc/my.cnf

server-id=2

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2. Restart the service to make the configuration file effective

/etc/init.d/mysqld restart

3. Configure the slave server

mysql> Change Master to master_host= ' 192.168.44.151 ', master_user= ' Bob ', master_password= ' 123456 ', master_log_file = ' mysql-bin.000002 ', master_log_pos=106;

ERROR 1198 (HY000): This operation cannot is performed with a running slave; Run STOP SLAVE First//Error

mysql> stop Slave;

Query OK, 0 rows Affected (0.00 sec)

mysql> Change Master to master_host= ' 192.168.44.151 ', master_user= ' Bob ', master_password= ' 123456 ', master_log_file = ' mysql-bin.000002 ', master_log_pos=106;

Query OK, 0 rows affected (0.06 sec)

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:

master_host:192.168.44.151

Master_user:bob

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:106

relay_log_file:b-relay-bin.000001

Relay_log_pos:4

relay_master_log_file:mysql-bin.000002

Slave_io_running:no

Slave_sql_running:no

Replicate_do_db:test_1

Replicate_ignore_db:mysql

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:106

relay_log_space:106

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

Seconds_behind_master:null

Master_ssl_verify_server_cert:no

last_io_errno:1130

Last_io_error:error connecting to master ' [email protected]:3306 '-retry-time:60 retries:86400

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

4. Start the Copy from server function

mysql> start slave;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.44.151

Master_user:bob

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:106

relay_log_file:b-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:test_1

Replicate_ignore_db:mysql

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:106

relay_log_space:402

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

Five, master-slave synchronization test:

The primary server creates the library test_1, and the test_1 is also created from the server

mysql> CREATE DATABASE test_1;

Query OK, 1 row Affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Test |

| test_1 |

+--------------------+

4 rows in Set (0.00 sec)

mysql> use test_1;

Database changed

Mysql> CREATE TABLE Bob (id int (1), name char (5));

Query OK, 0 rows affected (0.10 sec)

Mysql> INSERT INTO Bob values (1, ' Bob ');

Query OK, 1 row affected (0.05 sec)

Mysql> select * from Bob;

+------+------+

| ID | name |

+------+------+

| 1 | Bob |

+------+------+

1 row in Set (0.00 sec)

VI. The master never synchronizes the solution

First look at the master:

Mysql>show processlist; See if the next process is too much sleep. Found to be normal.

Show master status; Normal.

Mysql> Show master status;

And then to the slave, see:

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:no

Visible is slave different steps

Method one: After ignoring the error, continue syncing

This method is applicable to the case that the data of master-slave database is not very different, or the data can not be completely unified, the data requirements are not strict.

Mysql>stop slave;

Skip means skipping one-step error, followed by a variable number

Mysql>set Global sql_slave_skip_counter = 1;

Mysql>start slave;

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

Method Two: Complete synchronization, this method is suitable for the master-slave database data is large difference, or requires the data is completely unified situation

1, first enter Master to lock the table, to prevent data write

Mysql> flush tables with read lock;

2. Then export the data for data backup

[[email protected]] #mysqldump-uroot-p-hlocalhost > Mysqlbak.sql

Note here: Database backups must be done on a regular basis, with shell scripts or Python scripts, to make sure the data is foolproof

3. View Master Status

Mysql> Show master status;

+-------------------+----------+--------------+-------------------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------+----------+--------------+-------------------------------+

| mysqld-bin.000001 | 3260 | | Mysql,test,information_schema |

+-------------------+----------+--------------+-------------------------------+

1 row in Set (0.00 sec)

4. Upload the MySQL backup file to the library machine for data recovery

[Email protected]]# SCP mysqlbak.sql [email protected]:/tmp/

5. Stop the state from the library

mysql> stop Slave;

6. Then execute MySQL command to slave, import data backup

Mysql> Source/tmp/mysqlbak.sql

7, settings from the library synchronization, note that the synchronization point, is the main library show Master status information | file| Position two items

Change Master to Master_host = ' 192.168.128.100 ', master_user = ' rsync ', master_port=3306, master_password= ', Master_log _file = ' mysqld-bin.000001 ', master_log_pos=3260;

8. Re-open from sync

mysql> start slave;

9. View sync Status

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

10. Unlocking

mysql> unlock tables;

This article is from "Kaka West" blog, please be sure to keep this source http://whnba.blog.51cto.com/1215711/1605720

MySQL Master-slave replication