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

Environment:

172.30.249.156 percona5.6.27 hostname: tr-mysql runs the virtual machine for applications in the production environment

192.168.129.153 percona5.6.27 hostname: trcloud-gtt-db newly applied physical machine

Work content: build the master-slave architecture for the production environment database, and put the master database on the newly applied physical machine. The continuous downtime should not exceed 10 minutes.

General process: build master-slave-- > master / slave switch-- > test results. Because the data of the production environment is about more than 100G, considering the downtime and efficiency, the recovery backup database uses Xtrabackup backup and recovery instead of mysqldump and cold backup.

First: build master and slave 1. Create replication account.

Set up a backup account in Master's database: each slave connects to master with a standard MySQL username and password. The user performing the replication operation is granted REPLICATION SLAVE permission.

The command is as follows:

Mysql > create user 'replicat'@'192.168.129.153' identified by' passw'

Query OK, 0 rows affected (0.03 sec)

Mysql > grant replication slave,super on *. * to 'replicat'@'192.168.129.153'

Query OK, 0 rows affected (0.01 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Set up an account backup and only allow login from the address 10.211.55.5 with the password passw.

2. Configure master and slave parameters

Main library:

Open the binary log, specify a unique server ID,

[root@localhost data] # vi / etc/my.cnf

……

Log-bin=/data/DB/mysql/mysql-bin

Server-id=1

…… .

Prepare the library:

Open binary log and specify a unique server ID

[root@localhost mysql] # vi / etc/my.cnf

……

Log-bin=/data/DB/mysql/mysql-bin

Server-id=2

…… .

3. Copy and restore the database data

Master library backup:

[root@tr-mysql backup] # xtrabackup-backup-user=root-datadir=/data/DB/mysql/-target-dir=/data/backup/20160628bak/

.

160628 09:55:17 Executing UNLOCK TABLES

160628 09:55:17 All tables unlocked

160628 09:55:17 Backup created in directory'/ data/backup/20160628bak/'

160628 09:55:17 [00] Writing backup-my.cnf

160628 09:55:17 [00]... done

160628 09:55:18 [00] Writing xtrabackup_info

160628 09:55:18 [00]... done

Xtrabackup: Transaction log of lsn (1696091795) to (1696091805) was copied.

160628 09:55:18 completed OK!

[root@tr-mysql backup] # xtrabackup-- prepare-- target-dir=/data/backup/20160628bak/-- make backup data consistent

[root@tr-mysql backup] # scp 20160628 bakram * root@192.168.129.153:/data/backup/20160628bak/

Prepare for library recovery:

[root@trcloud-gtt-db ~] # rsync-avrp / data/backup/20160628bak/ / data/DB/mysql/

.

Sent 6426459950 bytes received 259 bytes 197737237.20 bytes/sec

Total size is 6425674808 speedup is 1.00

[root@trcloud-gtt-db~] # chown-R mysql:mysql / data/DB/mysql

4. The main library prepares the test data

Mysql > use test

Mysql > create table id (id int (10))

Query OK, 0 rows affected (1.02 sec)

Mysql > insert into id values (1)

Query OK, 1 row affected (0.14 sec)

5. Set the replication process

View master's current binlog file

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000002 | 834 | |

+-+

1 row in set (0.00 sec)

Slave create replication process

Mysql > change master to master_host='172.30.249.156'

-> master_user='replicat'

-> master_password='passw'

-> master_log_file='mysql-bin.000002'

-> master_log_pos=0

6. Start slave to view the process

Start

Mysql > start slave

Query OK, 0 rows affected (0.03 sec)

View slave processes

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.30.249.156

Master_User: replicat

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 834

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 997

Relay_Master_Log_File: mysql-bin.000002

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: 834

Relay_Log_Space: 1171

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 7d7ee32c-26cd-11e6-8fe2-fa163ecb3078

Master_Info_File: / data/DB/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

The main purpose here is to see:

Slave_IO_Running=Yes

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

Both slave's Icano and SQL threads are running, and Seconds_Behind_Master is no longer a NULL. The location of the log has increased, which means that some events have been obtained and executed. If you make changes on master, you can see changes in the location of various log files on slave, as well as changes in data in the database.

View master processes

Mysql > show processlist\ G

.

* 3. Row * *

Id: 4

User: canal

Host: 172.30.248.95:35966

Db: NULL

Command: Binlog Dump

Time: 11821

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Rows_sent: 0

Rows_examined: 0

.

* * 5. Row * *

Id: 12

User: replicat

Host: 192.168.129.153:51948

Db: NULL

Command: Binlog Dump

Time: 614

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Rows_sent: 0

Rows_examined: 0

7. Test results

You can view the created tables and data on slave.

Mysql > select * from id

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Modify data on master

Mysql > update id set id=2 where id= 1

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1 Warnings: 0

View slave data

Mysql > select * from id

+-+

| | id |

+-+

| | 2 |

+-+

1 row in set (0.00 sec)

The test passed

Two: master-slave switching

1. Create a user on the slave library (the new master server)

Mysql > create user 'replicat'@'172.30.249.156' identified by' passw'

Query OK, 0 rows affected (0.03 sec)

Mysql > grant replication slave,super on *. * to 'replicat'@'172.30.249.156'

Query OK, 0 rows affected (0.01 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

2. Slave library (new master server) operation

Mysql > STOP SLAVE IO_THREAD

Query OK, 0 rows affected (0.03 sec)

Mysql > SHOW PROCESSLIST

+-+ -+

| | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+-+ -+

| | 1 | event_scheduler | localhost | NULL | Daemon | 2238 | Waiting on empty queue | NULL | 0 | 0 |

| | 5 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | 0 | 0 |

| | 7 | system user | | NULL | Connect | 809 | Slave has read all relay log; waiting for the slave thread to update it O thread to update it | NULL | 0 | 0 |

+-+ -+

Make sure the status is: has read all relay log

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 172.30.249.156

Master_User: replicat

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1523

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 1686

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: No

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: 1523

Relay_Log_Space: 1860

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 7d7ee32c-26cd-11e6-8fe2-fa163ecb3078

Master_Info_File: / data/DB/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

3. Change from library to master library

Mysql > STOP SLAVE

Query OK, 0 rows affected (0.03 sec)

Mysql > RESET MASTER

Query OK, 0 rows affected (0.12 sec)

Mysql > RESET SLAVE

Query OK, 0 rows affected (0.11 sec)

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000009

Position: 120

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

4. Master library to slave database

Mysql > RESET MASTER

Query OK, 0 rows affected (3.36 sec)

Mysql > RESET SLAVE

Query OK, 0 rows affected (0.00 sec)

Set up the slave process

Mysql > change master to master_host='192.168.129.153'

-> master_user='replicat'

-> master_password='passw'

-> master_log_file='mysql-bin.000009'

-> master_log_pos=120

Start the slave process

Mysql > start slave

5. Modify the parameters and restart

Change from library to read-only

Modify the current slave library parameters

Read-only = 1

Innodb_read_only = 1

Super_read_only = 1 # (cannot be modified even if you have super permission)

Restart

[root@tr-mysql mysql] # service mysql restart

6. Check whether the master and subordinate are normal.

Main library

SHOW PROCESSLIST

Show master status\ G

Slave library

SHOW PROCESSLIST

Start slave

Show slave status\ G

7. Test data

Main library

Mysql > update id set id=1 where id=2

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Slave library

Mysql > select * from id

+-+

| | id |

+-+

| | 1 |

+-+

Successfully switched over

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.