Using Percona-xtrabackup to quickly build MySQL database master-slave replication

Application Scenarios:

Linux Server A runs the MySQL service and opens the Log-bin (Log-bin = mysql-bin) and specifies the Server-id (Server-id = 1);

Linux Server B also in the same manner as Server A (not the same can, refer to the following "note") installed the MySQL service, can be opened Log-bin can also not turn on the Log-bin, if you turn on Log-bin you need to pay attention to MySQL function limitations (available through set global log_bin_trust_function_creators=true; resolution );

Replication mode: Server B is fully synchronized with Server A's MySQL (recommended for this, refer to the last paragraph in the article on database import failures due to disk block default retention policy for a description of database ha).

Attention:

Server A and Server B are best equipped with similar hardware configurations and database installation parameters. If the database is compiled and installed, then you need to pay attention to configuring libmysqlclient18, otherwise it will cause the manual installation of the Percona-xtrabackup run times error, saying that some running files could not be found.

Refer to:

# Set MySQL library ln-s/usr/local/mysql/lib/libmysqlclient.so.18.0.0/usr/lib64/libmysqlclient.so.18.0.0 ln-s/u sr/local/mysql/lib/libmysqlclient.so.18.0.0/usr/lib64/libmysqlclient.so.18 ln-s/usr/local/mysql/lib/ libmysqlclient.so.18.0.0/usr/lib64/libmysqlclient.so ls-l/usr/lib64/libmysqlclient.so.18.0.0 ls-l/usr/lib64/lib mysqlclient.so.18 ls-l/usr/lib64/libmysqlclient.so vim/etc/ld.so.conf.d/mysql-x86_64.conf/usr/lib64/mysq L/usr/local/mysql/lib Ldconfig

Tool Knowledge:

Percona-xtrabackup is an open source free MySQL backup tool that supports hot standby (that is, the backup process does not affect database use, IO and some CPU resources are still needed, but generally the impact on the database server is not very large, Especially when the data directory and backup directory of the database are the disks that are high-performance disks or different disks, the impact on them is minimal.

Its backup principle is similar to the cold copy database database directory, but do more granular, such as automatically ignore some unnecessary logs, files and so on.

The CentOS default source does not have percona-xtrabackup this toolkit, it can only be installed in a manual manner.

cd   wget -c https://www.percona.com/downloads/ xtrabackup/percona-xtrabackup-2.2.12/binary/tarball/percona-xtrabackup-2.2.12-linux-x86_64.tar.gz     tar zxf percona-xtrabackup-2.2.12-linux-x86_64.tar.gz     cd  percona-xtrabackup-2.2.12-linux-x86_64/bin    yum -y install perl  perl-time-hires    ln -s /root/percona-xtrabackup-2.2.12-linux-x86_64/bin/ innobackupex /usr/bin/innobackupex    ln -s /root/ Percona-xtrabackup-2.2.12-linux-x86_64/bin/xbcrypt /usr/bin/xbcrypt    ln -s  /root/percona-xtrabackup-2.2.12-Linux-x86_64/bin/xbstream /usr/bin/xbstream     Ln -s /root/percona-xtrabackup-2.2.12-linux-x86_64/bin/xtrabackup /usr/bin/xtrabackup 

Ubuntu provides percona-xtrabackup tools by default and can be installed directly using Apt-get, apt-get-y install Percona-xtrabackup.

Apt-get-y Install Percona-xtrabackup

Attention:

Installing the Percona-xtrabackup tool with Apt-get in the Ubuntu system will cause libmysqlclient18 Mysql-common the two packages will also be installed (Libaio1 Libdbd-mysql-perl Libdbi-perl libmysqlclient18 Mysql-common percona-xtrabackup), Mysql-common This package will be brought into the/etc/mysql/my.cnf, which may lead to service mysql {command} is not working properly, you can rename the/etc/mysql/my.cnf or rename the/etc/mysql directory to eliminate the impact. If the database is not compiled and installed instead of Apt-get installation, you can pay attention to see if there is any impact.

Operation steps:

1. Determine that the Log-bin is correctly configured on the master-slave database and assigned different Server-id

Egrep ' (log-bin|server-id) '/etc/my.cnf or Grep-e ' (Log-bin|server-id) '/etc/my.cnf

2. Use the Innobackupex tool on the primary library server to back up the database and transfer the database backup files to the disk from the library machine

# for master    apt-get install percona-xtrabackup     mv /etc/mysql/my.cnf /etc/mysql/my.cnf~ | |  mv /etc/mysql /etc/mysql~    service mysql status     # df -h    # du -sh /data/mysql/data      #  observe disk usage, and the size of the/data/mysql/data mysql database data directory, and assess where the SCP will go from the library server, and so on      cd /data    mkdir -p /data/mysql-master_data     # which innobackupex    #  to avoid SSH connection loss, use Nohup execution      nohup /usr/bin/innobackupex --user=root --password=password /data/mysql-master _data &     #上述命令执行成功后会在 The/data/mysql-master_data directory with the name of a new directory generated in the time format, such as/data/ mysql-master_data/2015-11-03_12-39-51    # make sure user privileges on scp target    # sshpass -  noninteractive ssh password provider    apt-get -y install  sshpass    #  to avoid SSH connection loss, you can use Nohup to perform     sshpass -ppassword  scp -o stricthostkeychecking=no -p22 -r /data/mysql-master_data [email  protected]:/data    rm -rf /data/mysql-master_data

3. Recover the database backup files from the primary library server using the Innobackupex tool from the library server

# for slave #此处参考文章下面的 "Xtrabackup backup rationale" Refer to Understanding why Apply-log Innobackupex--apply-log/data/mysql-master_data/2015-11- 03_12-39-51 #查看 the Xtrabackup_binlog_info file in "/data/mysql-master_data/2015-11-03_12-39-51", record bin-log filename and pos value,    To switch master. Service MySQL stop #清空数据库数据目录 rm-rf/data/mysql/data/* innobackupex--copy-back/data/mysql-master_data/2015-11- 03_12-39-51 chown Mysql:mysql-r/data/mysql/data/

4. Configure from the library, enable the synchronization process and observe the results

service mysql start    mysql -uroot -ppassword     #如果从库的my. CNF is set to open binary log, for some programmer's convenience, enable log_bin_trust_function_creators, further understanding can refer to the below "mysql  function limit" and " MySQL Create method Error: This function has none of deterministic, no sql "     SHOW VARIABLES LIKE  '%func% ';     set global log_bin_ trust_function_creators=1;    show variables like  '%func% ';     change master to master_host= ' 192.168.100.132 ', master_user= ' root ', master_password= ' Password ', master_log_file= ' mysql-bin.000665 ',master_log_pos=51145611;    start slave;     SHOW SLAVE STATUS \G;     #注意观察Slave_IO_Running:  yes and Slave_sql_running: yes, only when Yes is true     #  if an event is configured on the original main library, it needs to be stopped from the library. After all, from the library is not used to do this (contains write operations)     show variables like  ' Event_scheduler ';    set global  event_scheduler=0;    show variables like  ' Event_scheduler ';     quit;    rm -rf /data/mysql-master_data    #  end

An example of backing up a database and recovering a database using the Innobackupex tool:

# backup   cd /data     Mkdir -p /data/mysql-master_data    nohup /usr/bin/innobackupex --user =root --password=password/data/mysql-master_data &    ls /data/mysql/ Data/ -alh    ls /etc/my.cnf -l# recovery   service  mysql stop    service mysql status     Innobackupex --apply-log /data/mysql-master_data/2015-11-03_11-21-10/    rm  -rf /data/mysql/data/*    innobackupex --copy-back /data/mysql-master _data/2015-11-03_11-21-10/    chown mysql:mysql -r /data/mysql/data/     service mysql start    rm -rf /data/mysql-master_ Data 

Reference:

Percona-xtrabackup Introduction Https://www.percona.com/software/mysql-database/percona-xtrabackup

Analysis of Xtrabackup backup principle http://blog.chinaunix.net/uid-20785090-id-4212816.html

MySQL Replication Options Http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html

MySQL function Limit Http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_log_bin_trust_function_creators

MySQL Create method Error: This function has none of deterministic, NO SQL http://blog.sina.com.cn/s/blog_4cb400450100ntu6.html

Tag:mysql Database Master-slave configuration, Innobackupex Backup recovery database, xtrabackup Backup recovery database, Innobackupex Backup restore database, xtrabackup backup RESTORE Database

--end--

This article is from "Communication, My Favorites" blog, please make sure to keep this source http://dgd2010.blog.51cto.com/1539422/1709183

Using Percona-xtrabackup to quickly build MySQL database master-slave replication