Quickly build a slave Database Based on mysqldump

Quickly build a slave Database Based on 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. 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 3506.
Master @ localhost [(none)]> show variables like 'version ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Version | 5.6.12-log |
+ --------------- + ------------ +

Master @ localhost [(none)]> system cat/etc/issue
CentOS release 5.4 (Final)
Kernel \ r on an \ m

-- For the parameter Master/Slave number configuration, refer to MySQL replication brief description and example
-- Create an account for copying
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 location
Master @ localhost [(none)]> show master status;
+ -------------------- + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ -------------------- + ---------- + -------------- + -------------------- + ------------------- +
| Inst3406bin. 000001 | 2169 |
+ -------------------- + ---------- + -------------- + -------------------- + ------------------- +
1 row in set (0.00 sec)

-- Use mysqldump to export instances
Master @ localhost [(none)]> system mysqldump-uroot-pxxx-S/tmp/mysql3406.sock -- routines -- all-databases -- opt> alldb. SQL

Master @ localhost [(none)]> system ls
Alldb. SQL data3406

-- Unlock
Master @ localhost [(none)]> unlock tables;

Master @ localhost [(none)]> exit

-- Import dump from the database
[Mysql @ app inst3406] $ mysql-uroot-pxxx-S/tmp/mysql3506.sock <alldb. SQL

-- Set information about the master database from the database (host, port, etc)
[Mysql @ app inst3506] $ mysqls
Slave @ localhost [(none)]> change master
-> MASTER_HOST = '192. 168.1.177 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'xxx ',
-> MASTER_PORT = 3406,
-> MASTER_LOG_FILE = 'inst3406bin. 100 ',
-> MASTER_LOG_POS = 2169;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

-- Start slave
Slave @ localhost [(none)]> start slave;

2. Build some slave Databases

-- The following example shows how to set up only some slave databases to 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 results are as follows:
[Mysql @ app ~] $ Grep replicate/data/inst3506/data3506/my3506.cnf
Replicate-do-db = test
Replicate-do-db = sakila

[Mysql @ app ~] $ Grep skip-slave/data/inst3506/data3506/my3506.cnf
Skip-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 only sakila tempdb from the master database
[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 ~] $ Mysqls
Slave @ localhost [(none)]> source multidb. SQL

-- View the master binlog location during dump
Slave @ localhost [tempdb]> system grep-I "change master" multidb. SQL
-- Change master to MASTER_LOG_FILE = 'inst3406bin. 100', MASTER_LOG_POS = 000001;

-- Set information about the master database from the database (host, port, etc)
Slave @ localhost [tempdb]> change master
-> MASTER_HOST = '192. 168.1.177 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'xxx ',
-> MASTER_PORT = 3406,
-> MASTER_LOG_FILE = 'inst3406bin. 100 ',
-> 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: