MySQL master-slave Introduction, configuration Master-slave, test master

First, MySQL master-slave introduction

MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data real-time synchronization
MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.
The master-slave process is roughly 3 steps:
1) The change operation is recorded in Binlog.
2) from synchronizing the main binlog event (SQL statement) to the machine and recording it in Relaylog
3) Execute sequentially from the SQL statements inside the Relaylog
The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog
There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to drop the SQL statements inside the Relaylog.

MySQL master-slave is generally used in data backup and not only to back up and read data from the top. Can not go to write data from the upper host, only can read. This is one of their characteristics.

Ii. preparatory work

1. Prepare two machines and install the MySQL service.

Third, configure the main

1), modify MY.CNF, add server-id=130 and log_bin=riven1 (bin_log prefix)
Vim/etc/my.cnf

2), after modifying the configuration file, start or restart the Mysqld service
/etc/init.d/mysqld Restart after the restart is complete, a file prefixed with RIVEN1 is generated under/data/mysql.

3), backup and restore MySQL library to Riven1 library as test data
Backup blog database: Mysqldump-uroot-ppassword blog >/tmp/blog.sql
View Size: Du-sh/tmp/blog.sql
Create a library: mysql-uroot-e "CREATE Database Riven1"
4), restore the blog database to the RIVEN1 library: Mysql-uroot Riven1 </tmp/blog.sql
5), create a user to be used as the synchronization data:
Grant replication Slave on . to ' repl ' @slave_ip identified by ' password ';
(Give replication slave permissions only, specify from IP)
Lock table do not write data inside: Flush tables with read lock;
View table: Show Master status;
Remember file and position
Back up other databases:
Zrlog
Mysql

Iv. configuration from

1), view my.cnf, configuration server-id=132, requirements and the main not the same
Vi/etc/my.cnf
2), after modifying the configuration file, start or restart the Mysqld service
/etc/init.d/mysqld restart
3), synchronize the Lord Aming library to the top
scp 主ip：/tmp/*.sql /tmp/
And then into MySQL.
4), you can first create RIVEN1, blog, zrlog, restore them:
Exit database
Mysql-uroot Blog </tmp/blog.sql
Mysql-uroot Riven1 </tmp/riven1.sql
Mysql-uroot Zrlog </tmp/riven1.sql
View/data/mysql directory is not the same as the Lord's.
Ls/data/mysql
5), enter MySQL,
mysql-uroot-ppasswd
Stop slave;
Change Master to master_host= ' master IP ', master_user= ' repl ', master_password= ' password ', master_log_file= ' file ' in the Lord's Memory ', Master_ The position of Log_pos= 's Lord;
Start slave;
7), check whether the master/slave has been successfully configured:
Execute Mysql-uroot from top
Show Slave Stauts\g
See if there is
Slave_io_running:yes
Slave_sql_running:yes
Also need to pay attention to
seconds_behind_master:0//main from the time of the delay
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
There is no error message.
8), but also to the Lord to resume reading and writing, the implementation of unlock tables

Five, test master-Slave synchronization

1), on the primary server
binlog-do-db=//Synchronize only the specified libraries
binlog-ignore-db=//Ignore specified library
2), from the server
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
The top four can be used as much as possible
replicate_wild_do_table=//As aming.%, wildcard% supported
replicate_wild_ignore_table=

3), Lord Mysql-uroot Riven1
Switch libraries: Use RIVEN1;
View table: Show tables;
See how many lines: SELECT COUNT (*) from DB;

Empty a line:
TRUNCATE TABLE db;
Delete a table: drop table wp_users;
See if the table exists: SELECT COUNT (*) from wp_users;
To Mysql-uroot aming from the top

If you delete the table from the master, you can only do it again. Change Master.

Select COUNT (*) from DB;
The Lord continues to drop table db;
View the DB table from the top

MySQL master-slave Introduction, configuration Master-slave, test master