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