Innobackupex backup data to build MySQL Slave

Brief introduction:

When the data volume is large, the MySQL Slave node is added using Innobackupex backup data.

Install Innobackupex tool, I wrote here once: http://www.cnblogs.com/wangxiaoqiangs/p/5961413.html

Scene:

  E, D, C    , A- B    

First, increase the node C

# because there are from library B, so we go to B above to perform a backup

Shell > Innobackupex--user=xx--password=xx--slave-info --safe-slave-backup--no-timestamp alldatabase

# Save the Slave information for B, because the new node C and B peers

SCP -R alldatabase Mysql-nodec:/root

# Copy the backup data to the new node C, if node C is outside the network, we recommend compressing it first

# Login Node C

Shell > vim/etc/MY.CNF # Preparing the configuration file [Client]port=3306Socket=/tmp/Mysql.sock[mysqld]socket=/tmp/Mysql.sockdatadir=/data/Mysql_datalog-error =Error.logskip-name-Resolveskip-external-Lockingdefault-storage-engine =myisamkey_buffer_size=3072msort_buffer_size=256mread_buffer_size=4mread_rnd_buffer_size=8mmyisam_sort_buffer_size=128mtable_open_cache=1024x768Table_definition_cache=1024x768thread_cache_size=8Back_log= +Connect_timeout= -max_connections= theWait_timeout= -Interactive_timeout= -Max_allowed_packet=1024mslow_query_log=Onlong_query_time=2Slow-query-log-file=Slow.logquery_cache_type=0query_cache_size=0Query_cache_limit=0tmp_table_size=128mmax_heap_table_size=128minnodb_file_per_table=0innodb_buffer_pool_size=1024Mread-only =1Relay-log = mysql-relay-binexpire_logs_days=7Server-ID=1020

# Note: Server-id must be globally unique
# If you add node D, you need to replace the following 4 rows with the following 7 rows

Relay-log = mysql-relay-= ONlog1log-bin = mysql-=7 Server-ID1020

# because node D is not only the Slave of Master A, but also the master of Node E

Shell > Innobackupex--apply-Log Alldatabase # Apply logs on the backup directory161111  -: About:GenevaCompleted ok!Shell> Innobackupex--copy-back Alldatabase # Copy the backup file to my.cnf datadir the specified directory161111  -:Ten: +Completed ok!Shell>Chown-R mysql.mysql/data/Mysql_datashell>/etc/init.d/Mysql.server Startshell>Cat/data/mysql_data/Xtrabackup_slave_info # Record values for Master_log_file and Master_log_pos change master to Master_log_file='mysql-bin.003742', master_log_pos=1072788827Shell> Mysql-uxx-pxx-Amysql>Stop Slave;mysql>Reset Slave All;mysql> Change Master to master_host='x.x.x.x', master_user='xx', master_password='xx', master_log_file='mysql-bin.003742', master_log_pos=1072788827; MySQL>Start Slave;mysql>show slave status\g slave_io_running:yes slave_sql_running:yesmysql> Quit

# master_host= ' A '
# MySQL Slave node C built to complete

Second, the new node E

# because node D does not follow the node, the backup is performed on node D

Shell > Innobackupex--user=xx--password=xx--no-timestamp alldatabase

# We want information on node D, so we don't need to save Slave information (Slave information to synchronize with node A)

SCP -R alldatabase Mysql-nodee:/root

# Copy the backup data to the new node E, if the node E in the network, it is recommended to compress first

Shell > Mysql-uxx-pxx-a # Not the same as above, need to create a new authorized user MySQL> Grant replication Slave on * * to [email protected]'%'Identified by'xx'; # login Node Eshell> vim/etc/MY.CNF # Preparing the configuration file [Client]port=3306Socket=/tmp/Mysql.sock[mysqld]socket=/tmp/Mysql.sockdatadir=/data/Mysql_datalog-error =Error.logskip-name-Resolveskip-external-Lockingdefault-storage-engine =myisamkey_buffer_size=3072msort_buffer_size=256mread_buffer_size=4mread_rnd_buffer_size=8mmyisam_sort_buffer_size=128mtable_open_cache=1024x768Table_definition_cache=1024x768thread_cache_size=8Back_log= +Connect_timeout= -max_connections= theWait_timeout= -Interactive_timeout= -Max_allowed_packet=1024mslow_query_log=Onlong_query_time=2Slow-query-log-file=Slow.logquery_cache_type=0query_cache_size=0Query_cache_limit=0tmp_table_size=128mmax_heap_table_size=128minnodb_file_per_table=0innodb_buffer_pool_size=1024Mread-only =1Relay-log = mysql-relay-binexpire_logs_days=7Server-ID=10201

# same note Server-id globally unique

Shell >Chown-R mysql.mysql/data/Mysql_datashell>/etc/init.d/Mysql.server Startshell>Cat/data/mysql_data/Xtrabackup_infobinlog_pos= filename'mysql-bin.000001', Position'112094418'Shell> Mysql-uxx-pxx-Amysql>Stop Slave;mysql>Reset Slave All;mysql> Change Master to master_host='x.x.x.x', master_user='Rep', master_password='xx', master_log_file='mysql-bin.000001', master_log_pos=112094418; MySQL>Start Slave;mysql>show slave status\g slave_io_running:yes slave_sql_running:yesmysql> Quit

# master_host= ' D '
# master_user= ' Rep ' # Here's the user password, which is on node D, not on Node A
# MySQL Slave node E build completed

# When you do not use Innobackupex, you can use the following methods:
# stop Slave;
# flush tables with read lock;
# show Slave Status\g
# record Master_log_file,master_log_pos
# Open a new terminal, Scp-r/data/mysql_data x.x.x.x:/data/mysql_data, copy completed can unlock tables;
# Chown-r Mysql.mysql/data/mysql_data
#/etc/init.d/mysql.server Start
# mysql-uxx-pxx-a
# Chanage Master to ...
# start Slave;

# Mysql.sock if placed under DataDir, remember chmod 755/data/mysql_data, otherwise zabbix_agentd not connect to the database
# End

Innobackupex backup data to build MySQL Slave