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

This article mainly explains "the detailed introduction of MySQL master-slave replication". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "MySQL master-slave copy detailed introduction"!

I. Preface

With the continuous increase of application business data, the response speed of the application continues to decline. In the process of detection, it is not difficult to find that most of the requests are query operations.

At this time, we can expand the database to master-slave replication mode, separate read and write operations, and apportion requests among multiple databases, so as to reduce the access pressure on a single database, and then the application can be optimized. Put together a 328-page MySQLPDF document

This test uses two virtual machines: ip:192.168.2.21 (master) ip:192.168.2.22 (slave)

The principle of master-slave replication

The synchronization operation is implemented through three threads, and the basic steps are as follows:

The master server records updates to the data in the binary log (which is called binary log events)-the master library thread; copies the master library binary log to the local relay log (relay log) from the slave library-the slave library Ilex O thread; reads the events in the relay log from the library and replays them into the data-the slave library SQL thread. 3. Configure the main library # 3.1 to create users

For security reasons, you are going to create a new user to connect to the master library from the library.

# create user create user 'repl'@'%' identified by' repl';# authorization, and only grant replication and client access rights to grant replication slave,replication client on *. * to 'repl'@'%' identified by' repl';# 3.2 modify configuration file

1) add vim / etc/my.cnf under [mysqld]:

Log-bin = mysql-binlog-bin-index = mysql-bin.indexbinlog_format = mixedserver-id = 21sync-binlog = 1character-set-server = utf8

2) Save the file and restart the main library:

Service mysqld restart

Configuration instructions:

Log-bin: set the base name of the binary log file; log-bin-index: set the binary log index file name; binlog_format: control the binary log format, and then control the replication type. Three optional values-STATEMENT: statement replication-ROW: line replication-MIXED: mixed replication. The default option server-id: server sets unique ID, default is 1, and the last part of IP is recommended. Sync-binlog: the default is 0. To ensure that no data is lost, it needs to be set to 1, which is used to force the binary log to be synchronized to disk every time a transaction is committed. # 3.3 backing up master database data

If the master and slave databases have just been installed and the data are consistent, execute show master status directly to check the log coordinates.

If the main library can be shut down, copy all database files directly.

If the primary library is an online production library, mysqldump can be used to back up the data because it is available to all storage engines.

1) to get a consistent snapshot, you need to set a read lock on all tables:

Flush tables with read lock

2) obtain the coordinates of the binary log:

Show master status

Return the result:

+-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000001 | 120 | +- -+ 1 row in set (0.00 sec)

3) backup data:

# for transactional engine mysqldump-uroot-ptiger-- all-database-e-- single-transaction-- flush-logs-- max_allowed_packet=1048576-- net_buffer_length=16384 > / data/all_db.sql# for MyISAM engine, or for multi-engine hybrid database mysqldump-uroot-all-database-e-l-flush-logs-max_allowed_packet=1048576-net_buffer_length=16384 > / data/all_db.sql1 row in set (0.00 sec)

4) resume the write operation of the main database:

Unlock tables; IV. Configuration from library # 4.1 modify configuration file

1) add vim / etc/my.cnf under [mysqld]:

Log-bin = mysql-binbinlog_format = mixedlog-slave-updates = 0server-id = 22relay-log = mysql-relay-binrelay-log-index = mysql-relay-bin.indexread-only = 1slave_net_timeout = 10

2) Save the file and restart the slave library:

Service mysqld restart

Configuration instructions:

Log-slave-updates: controls whether updates on slave are written to binary logs. Default is 0; if slave is only used as a slave server, it does not need to be enabled; if slave is used as master for other servers, it needs to be enabled and used together with log-bin and binlog-format, so that slave reads logs from the master database and redoes them, and then records them in its own binary log. Relay-log: sets the base name of relay log files. Relay-log-index: set the relay log index file name; read-only: set slave to read-only, but users with super permission can still write; slave_net_timeout: set the network timeout, that is, how long it takes to test whether the master and slave are connected. The default is 3600 seconds, that is, 1 hour. If this value is too large in the production environment, we modify it to 10 seconds, that is, if the master-slave is interrupted for 10 seconds, the reconnection action will be triggered. # 4.2 Import backup data

If no backup is made in step 3.3, ignore this step.

Mysql-uroot-p

< /data/all_db.sql# 4.3 统一二进制日志的坐标 根据 3.3 步骤获取的坐标，统一到从库中： change master tomaster_host='192.168.2.21',master_user='repl',master_password='repl',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=120; 注意：此处使用的是新创建的账户。 # 4.4 启动主从复制 1）启动从库 slave 线程： start slave; 2）查看从服务器复制功能状态： show slave status\G; 返回结果： *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.21 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 120 此处只张贴部分返回结果。 结果说明： Slave_IO_Running：此进程负责 slave 从 master 上读取 binlog 日志，并写入 slave 上的中继日志。Slave_SQL_Running：此进程负责读取并执行中继日志中的 binlog 日志。 这两个进程的状态需全部为 YES，只要有一个为 NO，则复制就会停止。 当 Relay_Master_Log_File = Master_Log_File 且 Read_Master_Log_Pos = Exec_Master_Log_Pos 时，则表明 slave 和 master 处于完全同步的状态。 五、验证 使用一个简单的例子： 在主库创建名为 mysql_test 的数据库，如果同步成功，那么在从库中也能查询出名为 mysql_test 数据库。

At this point, I believe that everyone on the "MySQL master-slave copy detailed introduction" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.