Quick build from library based on mysqldump

MySQL master-slave is generally divided into 3 steps, one is to add the necessary parameters for copying from the instance and create a copy of the account, two in the main library to create a snapshot, three in the library to add to the main library IP, port, user name, password, binlog location. There are many types of snapshots for master-slave builds, such as using InnoDB hotbak,xtrabackup,mysqldump and using tar directly to create snapshots. This article mainly describes the use of the Mysqldump method to establish a snapshot, suitable for a database of not more than 20GB or so.

References related to this article:
Exporting a database using mysqldump
MySQL replication Brief description and examples
MySQL multi-instance configuration (i)
MySQL multi-instance configuration (ii)

1, the instance level of the master-slave construction

--Demo Environment, this article demonstrates a multi-instance based on the same host, the primary port is 3406, the port is 3506[email protected][(none)]> show variables like ' version '; +------- --------+------------+| variable_name | Value |+---------------+------------+| Version | 5.6.12-log |+---------------+------------+[email protected][(none)]> system Cat/etc/issuecentos Release 5.4 (Final) Kernel \ r on the \m--configuration, refer to MySQL replication brief description and example-Create an account for replication [email protected][(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 [email protected][(none)]> flush tables with read lock; Query OK, 0 rows affected (0.02 sec) [email protected][(none)]> system pwd/data/inst3406--Get master Binlog location [ email protected][(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)-- Export instances using mysqldump [email protected][(none)]> system Mysqldump-uroot-pxxx-s/tmp/mysql3406.sock--routines-- All-databases--opt >alldb.sql[email protected][(none)]> system Lsalldb.sql data3406--Unlock [email  protected][(none)]> unlock tables; [Email protected] [(None)]> exit--import dump[[email protected from the vault] inst3406]$ mysql-uroot-pxxx-s/tmp/mysql3506.sock < alldb.sql--information about the main library from the library (host,port, etc.) [[email protected] inst3506]$ mysqls[email protected][(none)]>      Change Master to-master_host= ' 192.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 slave[email protected][(none)]> start slave; 

--The following demo is for building only parts from the library, for synchronizing only Sakila tempdb 2 libraries--Reset from library [email protected][(none)]> stop slave; Query OK, 0 rows affected (0.01 sec) [email protected][(none)]> reset Slave all; Query OK, 0 rows affected (0.01 sec)--to add only synchronization Sakila tempdb 2 libraries from the library, the following is the modified result [[email protected] ~]$ grep replicate/data/ INST3506/DATA3506/MY3506.CNF replicate-do-db=testreplicate-do-db=sakila[[email protected] ~]$ grep skip-slave /data/inst3506/data3506/my3506.cnfskip-slave-start--Restart 3506 to make the configuration effective [[email protected] ~]$ Mysqladmin-uroot -pxxx-s/tmp/mysql3506.sock shutdown[[email protected] ~]$ mysqld_safe--defaults-file=/data/inst3506/data3506 /MY3506.CNF &--Export from the main library only Sakila tempdb[[email protected] ~]$ mysqldump-uroot-pxxx-s/tmp/mysql3406.sock-- Single-transaction--master-data=2-r--database Sakila tempdb>multidb.sql--executes the dump file from the library side [[email protected ] ~]$ mysqls[email protected][(none)]> source multidb.sql--View the master Binlog location during dump [email protected][ tempdb]> SYSTEM grep-i "Change master" multidb.sql--change Master to master_log_file= ' inst3406bin.000001 ', master_log_pos=3293117;      --Set the information about the main library from the library (Host,port, etc.) [email protected][tempdb]> change Master to, master_host= ' 192.168.1.177 ', -master_user= ' Repl ',---master_password= ' xxx ',--master_port=3406, master_log_file= ' Inst3 406bin.000001 ', master_log_pos=3293117; Query OK, 0 rows affected, 2 warnings (0.01 sec)--starts [email protected][tempdb]> from the library start slave; Query OK, 0 rows affected (0.01 sec)--author:leshami--blog:http://blog.csdn.net/leshami--Check results [email protected ][tempdb]> Show slave status \g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t: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:relay-bin.000002 relay_log_pos:285 Re lay_master_log_file:inst3406bin.000001 Slave_io_running:yes Slave_sql_running:yes R eplicate_do_db:test,sakila--Note that during the mysqldump of this article,--single-transaction is only valid for the InnoDB engine--if only--master-data is used, will open--lock-all-tablesthe--master-data and--single-transaction options can be used simultaneously, which provides a conve Nient-Make a online backup suitable for use prior to point-in-time recovery if tables is stored using the I Nnodb storage engine.

Quick build from library based on mysqldump