MySQL master-slave replication practice and deployment

This paper mainly introduces the realization of MySQL master-slave copy function under the environment of single-database multi-instance.

Basic Flow:

1.MySQL Master-Slave Replication Introduction

2. Master-Slave replication database Combat environment Preparation

3. Perform the operation configuration on the Master library master

4. The procedure that is performed on MySQL from the library

5. Start the sync switch from the library to test the active replication configuration

6.MySQL Master-Slave Replication configuration Steps Summary

Process one: MySQL master-slave Replication Introduction

The master-slave replication scheme for MySQL databases, similar to file-level replication using commands such as Scp/rsync, is the remote transmission of data, except that MySQL's master-slave replication is its own feature, without the use of third-party tools, and The master-slave copy of MySQL is not a direct copy of the file on the database disk, but is copied to the server to be synchronized by the logical Binlog log, and then the local thread reads the SQL statement inside the log and re-applies it to the MySQL database.

MySQL's master-slave replication is an asynchronous process (although in general the feeling is real-time), the data will be copied from a MySQL database (what we call Master) to another MySQL database (we call it slave), The process of implementing the entire master-slave replication between master and slave is done by three threads. There are two threads (SQL thread and I/O thread) on the slave side, and another thread (I/O thread) on the master side. To achieve the master-slave replication of MySQL, first must open the master side of the Binlog record function, otherwise it will not be implemented, to open the MySQL binlog recording function, can be my.cnf in the MySQL configuration file mysqld module ([mysqld] The "log-bin" parameter option is added to the parameter section after identification. Because the entire replication process is actually the slave side obtains the Binlog log from the master side, and then executes the various SQL operations that are recorded in the obtained Binlog log in the same order on slave.

The main summary of MySQL master-slave replication principle:

#主从复制是异步的逻辑的SQL语句级的复制.

#复制时, the main library has an I/O thread, which has two threads from the library, namely I/O and SQL threads.

#实现主从复制的必要条件是主库要开启记录binlog功能.

#作为复制的所有MySQL节点的server_id都不能相同.

#binlog文件只记录对数据库有更改的SQL语句 (changes from the contents of the primary database), no queries (such as select,show) statements are logged.

Process two: master-slave replication database Combat environment Preparation

1) Master-slave Replication Practice Preparation

In this paper, a single-machine database multi-instance environment for example explained. Configure MySQL multi-instance to refer to http://blog.51cto.com/13707680/2112502

The instance port information is as follows:

[Email protected] 3308]# NETSTAT-TNLP | grep 330

TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8675/mysqld

TCP 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 8271/mysqld

TCP 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 9256/mysqld

2) Define server roles for master-slave replication

Main Library (MySQL master): "IP is 192.168.1.72 port 3306"

From library (MySQL slave): "IP is 192.168.1.72 port is 3307"

From library (MySQL slave): "IP is 192.168.1.72 port is 3308"

Process three: Performing an operational configuration on the Master library master

1) Set the server_id value and turn on the Binlog function parameter

[Email protected] 3306]# egrep "Server_id|log_bin"/data/3306/my.cnf

Log_bin=/data/3306/mysql-bin

server_id=3306 #用于同步的每台机器或实例server_id都不能相同

The above two parameters must be placed under the Mysqld module, otherwise there will be an error.

2) Restart the main library MySQL service with the following command:

[Email protected] 3306]#/data/3306/mysqld restart

Mysqladmin:connect to server at ' localhost ' failed

Error: ' Access denied for user ' root ' @ ' localhost ' (using Password:no) '

The error here says to password, modify the startup script file

[Email protected] 3306]# grep stop=/data/3306/mysqld

stop= ' mysqladmin-uroot -pywxi123 -s/data/3306/mysql.sock shutdown '

[Email protected] 3306]#/data/3306/mysqld restart

MySQL already stoped [OK]

MySQL 3306 starting [OK]

3) Log in to the database and check the parameter changes as follows:

[Email protected] 3306]# mysql-uroot-pywxi123-s/data/3306/mysql.sock

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.5.32-log MySQL Community Server (GPL)

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Show variables like ' server_id ';

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

| variable_name | Value |

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

| server_id | 3306 |

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

1 row in Set (0.01 sec)

Mysql> Show variables like ' Log_bin ';

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

| variable_name | Value |

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

| Log_bin | On |

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

1 row in Set (0.00 sec)

#这样binlog功能就正常开启了.

4) Create an account for master-slave replication on the main library

Mysql-uroot-pywxi123-s/data/3306/mysql.sock

Grant Replication Slave on * * to ' rep ' @ ' 192.168.1.% ' identified by ' ywxi123 '; #rep为同步账号, *. * is all tables for all libraries, 192.168.1.% is an authorized host network segment. Use% to allow entire Network segment

Flush privileges; #刷新权限, the authorization permission is in effect

Check the Rep copy account command created by the main library and the results are as follows:

Mysql> Select User,host from Mysql.user where user= ' rep ';

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

| user | Host |

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

| Rep | 192.168.1.% |

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

1 row in Set (0.00 sec)

Mysql> Show grants for [email protected] ' 192.168.1.% ';

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

|                                                                                               Grants for [email protected]% |

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

| GRANT REPLICATION SLAVE on * * to ' rep ' @ ' 192.168.1.% ' identified by PASSWORD ' *1cdcfbe8f2b7facc6bf289a82f072ab9a2d90844 ' |

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

1 row in Set (0.00 sec)

5) Implement lock table read-only for primary database

Mysql> Flush table with read lock;

Query OK, 0 rows Affected (0.00 sec)

View the main library status after the lock table:

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000006 |              334 |                  | |

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

1 row in Set (0.00 sec)

#锁表这个操作就是为了更好的导出和备份数据

[Email protected] 3307]# mkdir-p/server/backup

[[email protected] 3307]# mysqldump-uroot-p ' ywxi123 '-s/data/3306/mysql.sock--events-a-B |gzip >/server/backup/m ysql_bak.$ (date +%f). sql.gz

#导出数据完毕后, unlock the main library, resume writable, and command the following:

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

Process four: The procedure that is performed on MySQL from the library

1) Set the SERVER_ID value and close the Binlog function parameter

If you do not do cascade replication from the library, and do not do replication, do not open binlog, turn on instead will increase the pressure from the library disk I/O and so on.

[Email protected] 3306]# egrep "Server_id|log_bin"/data/3307/my.cnf

#log_bin =/data/3307/mysql-bin

server_id=3307 #ID是唯一的

2) Restart 3307 from the database

[[email protected] 3307]#/data/3307/mysqld stop

[[email protected] 3307]#/data/3307/mysqld start

3) Login database Check the parameters of the change situation

Mysql> Show variables like ' Log_bin ';

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

| variable_name | Value |

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

| Log_bin | OFF |

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

1 row in Set (0.00 sec)

Mysql> Show variables like ' server_id ';

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

| variable_name | Value |

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

| server_id | 3307 |

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

1 row in Set (0.00 sec)

4) Restore data exported from the main library mysqldump to the slave library

The operation commands are as follows:

cd/server/backup/

Mysqldump-uroot-p ' ywxi123 '-s/data/3307/mysql.sock--events-a-B |gzip >/server/backup/mysql_bak.$ (Date +%F). sql. Gz

5) Configure replication Parameters on 3307 from the library

[Email protected] backup]# mysql-uroot-pywxi123-s/data/3306/mysql.sock-e "Show Master Status"

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000006 |              334 |                  | |

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

[Email protected] backup]# mysql-uroot-pywxi123-s/data/3307/mysql.sock <<eof

Change MASTER to

Master_host= ' 192.168.1.72 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' ywxi123 ',

Master_log_file= ' mysql-bin.000006 ',

master_log_pos=334;

Eof

#这个步骤的参数一定不能错, otherwise, the database replication configuration will fail

Process five: Start the sync switch from the library and test the active replication configuration

1) Start the master-slave copy switch from the library and view the replication status

[Email protected] data]# mysql-uroot-pywxi123-s/data/3307/mysql.sock-e "start slave;"

[Email protected]st data]# mysql-uroot-pywxi123-s/data/3307/mysql.sock-e "show slave status\g;" | Egrep "Io_running| Sql_running|_behind_master "

Slave_io_running:yes

Slave_sql_running:yes

seconds_behind_master:0

#这是最关键的三个状态参数, you must agree with the above, or the test will be wrong

2) test the master-slave copy result, plus a 3308 from the library

Write data on the main library, and then observe the data status from the library.

[Email protected] data]# mysql-uroot-p ' ywxi123 '-s/data/3306/mysql.sock-e "CREATE database ywxi;"

[Email protected] data]# mysql-uroot-p ' ywxi123 '-s/data/3307/mysql.sock-e "show databases like ' Ywxi '"

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

| Database (YWXI) |

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

| Ywxi |

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

[Email protected] data]# mysql-uroot-pywxi123-s/data/3306/mysql.sock-e "drop database ywxi;"

[Email protected] data]# mysql-uroot-pywxi123-s/data/3307/mysql.sock-e "show databases like ' Ywxi ';"

[Email protected] data]#

#根据测试可以判断, the master-slave library is synchronized.

The quick plus one from the Library 3308 command set is as follows:

sed/log_bin/' s/.*/#&1/g '/data/3308/my.cnf-i

Mysql-uroot-s/data/3308/mysql.sock-e "Show variables like ' Log_bin ';"

Mysql-uroot-s/data/3308/mysql.sock-e "Show variables like ' server_id ';"

cd/server/backup/

Mysqldump-uroot-s/data/3308/mysql.sock--events-a-B |gzip >/server/backup/mysql_bak.$ (Date +%F). sql.gz

Mysql-uroot-s/data/3308/mysql.sock <<eof

> Change MASTER to

> master_host= ' 192.168.1.72 ',

> master_port=3306,

> master_user= ' rep ',

> master_password= ' ywxi123 ',

> master_log_file= ' mysql-bin.000006 ',

> master_log_pos=334;

> EOF

Mysql-uroot-s/data/3308/mysql.sock-e "start slave;"

Mysql-uroot-s/data/3308/mysql.sock-e "show slave status\g;" | Egrep "Io_running| Sql_running|_behind_master "

Process six: MySQL master-slave replication configuration Steps Summary

The complete steps for MySQL master-slave replication configuration are as follows:

1) Prepare two databases or a single multi-instance environment to determine normal startup and login

2) Configure the MY.CNF file: The main library configuration log_bin and server_id parameters, from the library configuration server_id, this value cannot be as unique as the main library, it is generally not recommended to turn on the Log-bin function. After configuring the parameters, you must restart the service to take effect

3) Log in to the main library and increase the number of accounts that are synced from the library connection to the main library, for example: Rep, and authorize replication slave sync permissions

4) Log in to the main library, the whole Library lock table flush table with read lock (expires after the window is closed, the time-out parameter is set, the lock table is invalidated), and then show Master status to view the location status of Binlog

5) The new window, the Linux command Line backup to export the original database data, and copied to the server directory where the library resides. If the amount of database data is large and allows downtime, you can stop packaging without mysqldump

6) After exporting master data, perform unlock tables unlock main Library

7) Restore the data exported from the main library to the library

8) based on the show master status of the main library, check the location status of the Binlog, and perform the change master to ... from the library. Statement.

9) Open the copy switch from the library, that is, execute start slave; (to execute after the change statement, or not to insert the statement)

10) Check the sync status from the library show slave Status\g, and make the update test in the main library.

MySQL master-slave replication practice and deployment