Use Innobackupex to quickly build (fix) the MySQL Master/Slave Architecture

Use Innobackupex to quickly build (fix) the MySQL Master/Slave Architecture

There are many ways to build MySQL Master/Slave databases. The traditional mysqldump mode is one of the choices of many people. However, this method is not ideal for large databases. With Xtrabackup, you can quickly and easily build or repair the mysql Master/Slave architecture. This article describes how to use innobackupex to quickly build or repair a master-slave architecture. For your reference.

1. Complete Backup Based on the master database
# Mkdir-p/log/bakforslave
# Innobackupex -- user = root-password = *** -- socket =/tmp/mysql. sock \
-- Defaults-file =/etc/my. cnf/log/bakforslave -- parallel = 3 -- safe-slave-backup -- no-timestamp

2. Copy the database to the slave
# Tar-czvf bakforslave.tar.gz./bakforslave/
# Scp bakforslave.tar.gz robin@172.16.10.51 :~
# Scp/etc/my. cnf robin@172.16.10.51 :~ /Mymaster. cnf

3. Restore on the slave machine
### Decompress the packaged backup file from the backup machine
# Mv/home/robin/bakforslave.tar.gz/data
# Cd/data
# Tar-xvf bakforslave.tar.gz

### Prepare backup
# Innobackupex -- user = root-password = *** -- socket =/tmp/mysql. sock -- defaults-file =/home/robin/mymaster. cnf \
-- Apply-log -- use-memory = 4 GB/data/bakforslave

### If the slave database is repaired, stop the slave database before starting the slave database. Otherwise, skip
# Service mysqld stop

### Restore the backup data file
# Mv mysqldata mysqldatabk
# Mv bakforslave mysqldata
# Chown-R mysql: mysql mysqldata

### If a new slave database is built, you can change my. cnf of the master database to my. cnf of the local database,
### If it is to be repaired, you can directly use the original configuration file or modify it as needed.
# Cp/home/robin/mymaster. cnf/etc/my. cnf
# Vi/etc/my. cnf ### use a different server_id here, and modify the relevant path and port configuration as needed.
# Service mysqld start ### after modification, you can start mysqld

4. master database authorization users used for replication
Mysql> grant replication slave, replication client on *. * to repl2 @ '192. 172.% 'identified '***';

### Log on to the master database at the shell prompt
# Mysql-urepl2-p-h172.16.10.88

5. Start slave
# More/data/mysqldata/xtrabackup_binlog_info
Mysql-bin.000136 73752825

Mysql> CHANGE MASTER
MASTER_HOST = '1970. 16.10.88 ', -- Author: Leshami
MASTER_USER = 'repl2', -- Blog: www.bkjia.com
MASTER_PASSWORD = '***',
MASTER_LOG_FILE = 'mysql-bin.000136 ',
MASTER_LOG_POS = 73752825;

Mysql> start slave;

6. Verification results
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.88
Master_User: repl2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000136
Read_Master_Log_Pos: 96592981
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 72113
Relay_Master_Log_File: mysql-bin.000136
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: test, bs_com, bs_sysmsg, bs_bak
Replicate_Ignore_DB: mysql
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: 73824655
Relay_Log_Space: 22840613
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: 3815
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: 2
Master_UUID: afd6bca4-6636-11e3-9d60-74867ae1c47c
Master_Info_File:/data/mysqldata/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: updating
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)

-------------------------------------- Split line --------------------------------------

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: