MySQL master-slave copy operation

Pre-Preparation: Two servers to install MySQL, or two MySQL instances to be deployed on a single server.

In order to avoid unnecessary errors, it is best to keep the MySQL version consistent.

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

| server Address | host name | database version | database port |server_id | role |

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

|192.168.175.248 | Mysql-248 | Mysql-5.6.30 |3306 | | Main Library master|

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

|192.168.175.249 | Mysql-249 | Mysql-5.6.30 |3306 | From the library slave |

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

First, the main library configuration:

1. Turn on the binary log and configure the server_id (requires a reboot to take effect).

[Email protected] mysql-5.6.30]# grep-a3 ' mysqld ' my.cnf

[Mysqld]

Port = 3306

server_id = 1

Log-bin=mysql-bin

Verify the binary log status, on for open:

Mysql> Show variables like ' Log_bin ';

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

| variable_name | Value |

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

| Log_bin | On |

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

1 row in Set (0.00 sec)

2. Create a MySQL replication user in the main library.

mysql> grant replication Slave on * * to ' repl_user ' @ ' 192.168.175.% ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

3. In the main Vault lock table Backup, and then unlock.

Lock table, the current window cannot be closed after the lock table :

Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

To view the master status information:

Mysql> Show master status;

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

| File | Position | binlog_do_db| binlog_ignore_db | Executed_gtid_set |

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

|    mysql-bin.000001 |         414 |            |             | |

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

1 row in Set (0.00 sec)

Create a new SSH window to back up the database:

[Email protected] ~]# mysqldump-uroot-p ' qwe123 '-A >/tmp/master248.sql

After the backup is complete, unlock it in the original window:

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

Second, from the library configuration:

1. Configure the server_id from the library and Relay-log(requires a reboot to take effect).

Note: The server_id must be unique and cannot be the same as other MySQL libraries. There is no need to turn on the binary log from the library.

[Email protected] mysql-5.6.30]# grep mysqld-a3 my.cnf

[Mysqld]

Port = 3306

server_id = 2

Relay-log = Mysql-relay-bin

2. Copy the backup of the main library to this machine and import the database.

Copy backup:

[Email protected] mysql-5.6.30]# SCP [email protected]:/tmp/master248.sql/tmp/

[email protected] ' s password:

Master248.sql

Import:

[Email protected] mysql-5.6.30]# mysql-uroot-p ' qwe123 ' </tmp/master248.sql

Warning:using a password on the command line interface can is insecure.

3. Specify the master server information and turn on slave.

Specify the Master information:

mysql> change Master to \

Master_host= ' 192.168.175.248 ',

Master_user= ' Repl_user ',

-master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=414;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

Open slave:

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

Third, check the master-slave replication:

1. Use show slave status\g from the library to query the main library information as well as the IO process, SQL process working status.

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.175.248

Master_user:repl_user

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:414

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

......

1 row in Set (0.00 sec)

The query results display Slave_io_running:yes,slave_sql_running:yes, indicating that the current master-slave replication status is normal.

2. In master new database, in slave query, test the master-slave copy effect.

Master builds a table.

Mysql> CREATE database Cubix character set UTF8;

Query OK, 1 row Affected (0.00 sec)

Mysql> Use Cubix

Database changed

Mysql> CREATE TABLE T1 (id int);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO T1 VALUES (' 1 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO T1 VALUES (' 2 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO T1 VALUES (' 3 ');

Query OK, 1 row affected (0.01 sec)

Slave query the newly created library.

mysql> show databases;

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

| Database |

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

| information_schema|

| Cubix |

| MySQL |

| performance_schema|

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

6 rows in Set (0.00 sec)

Mysql> Use Cubix

Database changed

Mysql> Show tables;

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

| Tables_in_cubix|

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

| T1 |

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

1 row in Set (0.00 sec)

Mysql> select * from T1;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 Rows in Set (0.00 sec)

Check the discovery of the new data on the main library, also from the library, also can prove that master-slave synchronization is normal.

This article comes from "Look back in a year" blog, make sure to keep this source http://cubix.blog.51cto.com/7251166/1842873

MySQL master-slave copy operation