Linux MySQL master-slave replication configuration

1. Set the Servie-id value of main Library master and turn on Bin-log function parameter
Vi/etc/my.cnf
To modify the parameters of the MY.CNF:
[Mysqld]
Server-id=1 //Each library must have a different Server-id
Log-bin=mysql-bin

2. Check that the parameters are set and restarted successfully
Egrep "Server-id|log-bin"/etc/my.cnf
Restart command see: MySQL installation or MySQL database multi-instance

3, log on the database to check the change of parameters
Show variables like ' server_id ';
Show variables like ' Log_bin ';

4. Create an account to copy from the library on the main library
From the library to copy data from the main library, you must have a master library account to allow access from the library
Mysql-uroot-p ' zjl123 '
mysql> grant replication Slave on * * to ' rep ' @ ' 10.0.0.% ' identified by ' zjl123 ';
mysql> flush Privileges;
Here, * * represents all tables for all databases, or it can indicate a table in a database
Rep is a synchronized account, 10.0.0.% is an authorized host network segment and uses% to allow the entire 10.0.0.0 network segment to access the rep user
Replication Slave is the permissions required for master-slave replication

5. Check if the rep copy account created by the main library is successful
Mysql>select user,host from Mysql.user;
Mysql>select user,host from mysql.user where user = ' rep ';
Mysql>show grants for [email protected] ' 10.0.0.% ';

Implementation of the main Library lock table read-only, used to back up the main library of data to from the library, so that the data consistent before synchronization, lock the table if the setting time is not operation will automatically unlock
6. Database lock table and view auto unlock time parameter:
Mysql>flush table with read lock;
Show variables like '%timeout% '

7. View the current Binlog file name and backup point of the main library, starting from this point when requesting replication from the library
Mysql>show Master status;
Or
Mysql-u root-p ' zjl123 '-e "show Master Status"

After the lock table must open an SSH window, export the main library data, if the data is large (more than 50G), and allow downtime, you can stop the library directly packaged data files for migration, that much faster.
8, backing up the main library data:
mkdir/server/backup/-P
Mysqldump-u root-p ' zjl123 '--events-a-B |gzip >/server/backup/mysql_bak.$ (date +%f). sql.gz
Ls-l/server/backup /mysql_bak.$ (date +f%). sql.gz
or:
mysqldump-u root-p ' zjl123 '--events-a-b-x--master-data=1 |gzip >/server/ backup/mysql_bak.$ (date +%f). sql.gz
--master-data=1 parameter adds the following statement to the backup data
Span style= "COLOR: #99cc00" >--position to start replication or point-in-time recovery from change master to Master_log_fi Le= ' mysql-bin-000005 ', master_log_post=107;
There is no need to specify the Binlog file name and location from the library at Change master, because the statement was backed up at backup time

9, in order to ensure that the database is not inserted during the export of data, the Guide library can be checked again to check the status of the main library, the results are as follows:
Mysql-u root-p ' zjl123 '-e "show Master Status"
If there is no special case, Binlog file and location point and lock table after the export data is consistent

10. Unlock database table
Mysql>unlock tables;

Here are the actions taken from the library
11. Set Server-id value and turn off Binlog function
The Server-id of a database is usually unique within a set of master-slave replication systems, where the Server-id from the library is different from the main library and other slave libraries, and the Binlog parameter configuration from the endangers is commented out, if the cascade copy is not required from the library and is not used for backup purposes, You do not need to turn on the Binlog function, but it will increase the I/O pressure from the library disk.
However, there are two situations in which you need to open the Binlog feature from the library to record the SQL statements for database updates:
1, as a form: a-->b-->c such cascade synchronization, the middle of the B database, it is necessary to open the Binlog function.
2, in the need to backup from the library to turn on the Binlog function, because the database backup must have, full and Binlog logs, is the full backup.

12. Import the data from the main library msqldump to the library
cd/server/backup/
Ls-l
ll
Mysql-uroot-p ' zjl123 '-s/data/3307/mysql.sock < Mysql_bak.2015-07-27.sql
Tip: If you use the-a parameter for the backup, the password for the 3307 instance will also be the same as the 3306 Main Library when you revert to 3307, because the authorization table for the 3307 instance is also covered by MySQL.

13. After logging in to the 3307 database, configure the replication parameters
Change MASTER to
Master_host= ' 10.0.0.7 ', #主库的IP
master_port=3306,#主库的端口
Master_user= ' rep ',#主库上建立的用于复制的用户rep
Master_password= ' zjl123 ',#rep的密码
Master_log_file= ' mysql-bin.000008 ',name of #binlog二进制文件
master_log_pos=342; #binlog二进制文件 Offset, which is the copy start point

The above command needs to be logged in MySQL and executed in MySQL.

You can also do this by using an internal command line that does not log on to the database, such as entering a command at the command line:
Mysql-uroot-p ' zjl123 '-s/data/3307/mysql.sock<< EOF
Change MASTER to
Master_host= ' 10.0.0.7 ',
master_port=3306,
Master_user= ' rep ',
Master_password= ' zjl123 ',
Master_log_file= ' mysql-bin.000008 ',
master_log_pos=342;
Eof

The above operation principle is the user, password, the main library port, IP and other information written from the library's Master.info file.
View the write results as follows:
Ll/data/3307/data/master.info
Cat/data/3307/data/master.info

14. Start the sync switch from the library and view the sync status
Mysql-uroot-p ' zjl123 '-s/data/3307/mysql.sock-e "start slave;"
Mysql-uroot-p ' zjl123 '-s/data/3307/mysql.sock-e "show slave status\g;"

Master-slave replication is successful mainly see the following 3 items of state
Mysql-uroot-p ' zjl123 '-s/data/3307/mysql.sock-e "show slave status\g;" | grep "Io_running| Sql_running|_behind_master "
What should be displayed separately is: Yes Yes 0

Slave_io_running:yes This is the IO thread state, the IO thread is responsible for reading the Binlog log from the library to the main library, and writing the relay log from the library, with a status of yes indicating that the IO thread is working properly.

Slave_sql_running:yes This is the SQL thread state, the SQL thread is responsible for reading the data in the relay log (Relay-log) and converting it to a SQL statement applied to the from database, with a state of Yes indicating that the SQL thread is working properly.

Seconds_behind_master:0, this is the number of seconds from the Cubby Main library delay during the copy process, this parameter is important, but the enterprise is more accurate to determine the master-slave replication delay method is: Write time stamp in the main library, then read the timestamp from the library, and the current database time to compare, Thus determining whether there is a delay.

The above is the master-slave copy of the entire content, test master-slave replication, only need to make changes in the main library, to see whether the same changes from the library to make.

15. View MySQL master-slave replication I/O thread status
Mysql>show processlist\g

16, problem one
When the main library statement is executed from the library when there is a conflict, such as the main library created a table, from the library already exists the name of the table, will be created unsuccessful, this time, master and slave synchronization can not continue
Solve:
Show Slave Status\g
stop slave; #临时停止同步形状
Set global sql-slave-skip-counter=1; #将同步指针移动一个, you can repeat the operation if you cannot synchronize
Start slave;
Set global sql-slave-skip-counter=n; #n >0, ignoring n update execution
Solution Two:
You can configure the configuration file in advance based on the error number, skipping the specified error that does not affect the business data, for example:
grep slave-skip/data/3306/my.cnf
Slave-skip-errors = 1032,1062,1007

17 How to log Binlog logs from the library:
Add the following parameters to the MY.CNF from the library and restart the service
Log-slave-updates#必须要有这个参数
Log-bin=/data/3307/mysql-bin
expire_logs_days=7# equivalent to find/data/3307/-type f-name "mysql-bin.000*"-mtime +7 | Xargs rm-f

Linux MySQL master-slave replication configuration