MySQL added from library

Project Background Description:

At the beginning of the project there is only one MySQL instance running, later due to security, stress, backup and other reasons need to add one from the base above this instance.

Analysis:

MySQL master-Slave is based on the Binlog log to achieve, then need the primary server to turn on Binlog, this option is off by default. My side of the server is on when it is deployed, because using Binlog can be used to recover MySQL data and be prepared for future decisions, so we recommend that you turn this option on when you deploy the master server. In addition, in the master from the time also need the main library and from the library Server-id unique, Server-id in the decision from the time is also a very important parameter.

In addition, because our MySQL main library has been running for some time, there are already quite a lot of data, we need to back up this data, and then from the library to synchronize data from the backup node, so as to maintain the master-slave data consistency, and in the operation of the best not to affect the normal operation of our business. Finally decided to use Xtrabackup to back up the data, because the xtrabackup back up the data without the need for a table, but limited to InnoDB engine database and XTRADB engine database, for the MyISAM engine database will still be a trivial table, Just right, our database engine is using INNODB.

Operation Steps:

The first step: Deploy from the database server, the best database version consistent, the deployment process is omitted

Step Two: Modify the configuration file

# below the main server needs to modify the configuration, server_id not necessarily 82, can be any number, for example, we can use the IP of this machine last Log_bin = Mysql-bindatadir=/data/mysql #此选项一定要指定, Even if there is a default you need to specify SERVER_ID = 82# The following options are available binlog-ignore-db =mysql,test #指定忽略不同步的数据库binlog-do-db=discuz,phpcms #指 The database that you want to synchronize

Authorize a master-slave sync account from the server above the primary server

mysql> grant replication Slave on * * to ' username ' @ ' hostip ' identified by ' password '

#  The following is a configuration that needs to be modified from the server datadir=/data/mysql                    #此选项一定要指定, even if there is a default, you need to specify server_id = 2#  The following options are available replicate-do-db                      #指定需要同步的数据库replicate-ignore-db                    #指定不同步的数据库replicate-do-table                    # Specify the tables that need to be synchronized replicate-ignore-table                    #指定不同步的表replicate-wild-do-table                    #指定需要同步的表, you can use wildcard characters, such as test.tables1* Replicate-wild-ignore-table                   #指定不需要同步的表, wildcard characters can be used 

Step three: Install the Xtrabackup tool

New Yum Source

Cat >>/etc/yum.repos.d/xtrabackup.repo << eof[percona]name = CentOS $releasever-perconabaseurl=http:// repo.percona.com/centos/$releasever/os/$basearch/enabled = 1gpgkey = file:///etc/pki/rpm-gpg/ Rpm-gpg-key-perconagpgcheck = 0EOF

Note: After executing the above command note the following file, it is possible $releaserver $basearch need to manually add

Installing Xtrabackup

Yum-y Install Percona-xtrabackup

Fourth step: Back Up your data

Note: Use the root user to execute, or use sudo permissions to execute

Backing up the entire library

Xtrabackup--user=root--password=root--target-dir=/opt/backups--backup

--user specifying the database access user name

--PASSWORD Specifies the database access password, if the password has special characters that need to be enclosed in single quotation marks

--target-dir Specify the backup path, preferably write absolute path

--backup with the--target-dir option

Backing up a single library

Xtrabackup--user=root--password=root--databases=app_test--target-dir=/opt/backups--backup

--databases Specify the name of the library to be backed up

Backing up multiple libraries

Xtrabackup--user=root--password=root--databases= "App_test WebService"--target-dir=/opt/backups--backup

--databases multiple libraries are enclosed in double quotes, separated by spaces

Backing up a specified table for a library

Xtrabackup--user=root--password=root--databases= "Nirvana.user_info nirvana.card_info"--target-dir=/opt/backups- -backup

--databases The backup table uses the library name. The way the table name is, if multiple tables are enclosed in double quotes, separate tables with spaces

Fifth step: Copy the backup data to the slave server

scp-pr/opt/backups [Email protected]:/path/to

Sixth step: Restore the data to the slave server

Before restoring, you need to empty the data directory from the server and back up to the place if you have the necessary data.

Entire library restore

Restore a single library

Restore multiple libraries

Restore a table

Execute the following command

Xtrabackup--copy-back--target-dir=/path/to

Seventh step: Enable Master-Slave

Execute the following command

mysql> Change Master to master_host= ' 192.168.1.71 ', master_port=3306,master_user= ' test ', master_password= ' 123456 ' , master_log_file= ' mysql-bin.000004 ', master_log_pos=107;

Master_host:

Master_port:

Master_user:

Master_password:

Master_log_file:

Master_log_pos:

Execute the following command to enable master-slave synchronization

mysql> start slave;

Tip: Stop the master-slave synchronization command for

mysql> stop Slave;

Seventh Step: Test and check the master and slave status

Mysql> show slave status \g;

If you look at the following two values for Yes, master-slave synchronization is OK

Slave_IO_Running:YesSlave_SQL_Running:Yes

Eighth Step: Test

Can be modified under the need to synchronize the library, or if the data of the table can be properly synchronized, or to modify the data when the backup is complete, when the backup is not the same record, when the master-slave synchronization is enabled to see if the data will be synchronized.

MySQL added from library