Quickly build a slave Database Based on mysqldump and mysqldump

Quickly build a slave Database Based on mysqldump and mysqldump

Mysql master-slave architecture is generally divided into three steps: one is to add the parameters required for replication to the master instance and the other is to create a snapshot in the master database, third, add the IP address, port, user name, password, and binlog location pointing to the master database to the slave database. There are many ways to create snapshots for master and slave databases, such as using InnoDB hotbak, xtrabackup, mysqldump, and using tar directly to create snapshots. This article describes how to create a snapshot using mysqldump. It is applicable to databases of no more than 20 GB.

References related to this article:
Use mysqldump to export a database
Brief description and example of MySQL Replication
MySQL multi-instance configuration (1)
MySQL multi-instance configuration (2)

1. instance-level Master/Slave Construction

-- Demo environment. In addition, this article demonstrates multiple instances based on the same host. The master port is 3406, And the slave port is 3506master @ localhost [(none)]> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + master @ localhost [(none)]> system cat/etc/issueCentOS release 5.4 (Final) Kernel \ r on an \ m -- about parameter Master/Slave number configuration, for a brief description and example of MySQL replication, create an account master @ localhost [(none)]> grant replication slave on *. * to 'repl' @ '192. 168.1.177 'identified by 'xxx'; Query OK, 0 rows affected (0.01 sec) -- Global read lock master @ localhost [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.02 sec) master @ localhost [(none)]> system pwd/data/inst3406 -- Obtain the master binlog position master @ localhost [(none)]> show master status; + bytes + ---------- + -------------- + ---------------- + bytes + | File | Position | Binlog_Do_DB | bytes | + bytes + ---------- + -------------- + ------------------ + bytes + | inst3406bin. 000001 | 2169 | + -------------------- + ---------- + -------------- + ------------------ + ------------------- + 1 row in set (0.00 sec) -- use mysqldump system mysqldump-uroot-pxxx-S/tmp/mysql3406.sock -- routines -- all-databases -- opt> alldb. sqlmaster @ localhost [(none)]> system lsalldb. SQL data3406 -- unlock master @ localhost [(none)]> unlock tables; master @ localhost [(none)]> exit -- import dump [mysql @ app inst3406] $ mysql-uroot-pxxx-S/tmp/mysql3506.sock <alldb. SQL -- set information about the master database (host, port, etc.) from the database [mysql @ app inst3506] $ mysqlsslave @ localhost [(none)]> change master to-> MASTER_HOST = '2017. 168.1.177 ',-> MASTER_USER = 'repl',-> MASTER_PASSWORD = 'xxx',-> MASTER_PORT = 3406,-> MASTER_LOG_FILE = 'inst3406bin. 000001 ',-> MASTER_LOG_POS = 2169; Query OK, 0 rows affected, 2 warnings (0.01 sec) -- start slaveslave @ localhost [(none)]> start slave;

2. Build some slave Databases

-- The following example shows how to set up only some slave databases and synchronize only two sakila tempdb databases -- reset slave database slave @ localhost [(none)]> stop slave; Query OK, 0 rows affected (0.01 sec) slave @ localhost [(none)]> reset slave all; Query OK, 0 rows affected (0.01 sec) -- add and synchronize only two sakila tempdb databases to the slave database. The modified result is as follows: [mysql @ app ~] $ Grep replicate/data/inst3506/data3506/my3506.cnf replicate-do-db = testreplicate-do-db = sakila [mysql @ app ~] $ Grep skip-slave/data/inst3506/data3506/my3506.cnfskip-slave-start -- restart 3506 after modification to make the configuration take effect [mysql @ app ~] $ Mysqladmin-uroot-pxxx-S/tmp/mysql3506.sock shutdown [mysql @ app ~] $ Mysqld_safe -- defaults-file =/data/inst3506/data3506/my3506.cnf & -- export sakila tempdb from the master database only [mysql @ app ~] $ Mysqldump-uroot-pxxx-S/tmp/mysql3406.sock -- single-transaction -- master-data = 2-R -- database sakila tempdb> multidb. SQL -- log on to the slave database and execute the dump file [mysql @ app ~] $ Mysqlsslave @ localhost [(none)]> source multidb. SQL -- view the binlog location of the master During dump. slave @ localhost [tempdb]> system grep-I "change master" multidb. SQL -- CHANGE MASTER TO MASTER_LOG_FILE = 'inst3406bin. 000001 ', MASTER_LOG_POS = 3293117; -- set information about the master database (host, port, and so on) from the slave database. slave @ localhost [tempdb]> change master to-> MASTER_HOST = '2017. 168.1.177 ',-> MASTER_USER = 'repl',-> MASTER_PASSWORD = 'xxx',-> MASTER_PORT = 3406,-> MASTER_LOG_FILE = 'inst3406bin. 000001 ',-> MASTER_LOG_POS = 3293117; Query OK, 0 rows affected, 2 warnings (0.01 sec) -- start slave database slave @ localhost [tempdb]> start slave; Query OK, 0 rows affected (0.01 sec) -- Author: Leshami -- Blog: http://blog.csdn.net/leshami--check result slave@localhost?tempdb]> show slave status \ G **************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.177 Master_User: repl Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin. 000001 Read_Master_Log_Pos: 3293117 Relay_Log_File: The relay-bin.000002 Relay_Log_Pos: 285 Relay_Master_Log_File: inst3406bin. 000001 Slave_IO_Running: Yes Slave_ SQL _Running: Yes Replicate_Do_DB: test, sakila -- note that during mysqldump in this article, -- single-transaction is only valid for the innodb engine -- if only -- master-data is used, -- lock-all-tablesThe -- master-data and -- single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine.