Shulou(Shulou.com)06/01 Report--

Project background description:

There is only one MySQL instance running at the beginning of the project, and a slave library needs to be added to this instance later because of security, stress, backup and other reasons.

Analysis:

MySQL master and slave is based on binlog logs, so you need to enable binlog on the master server. This option is turned off by default. My server is enabled when it is deployed, because binlog can be used to recover MySQL data and prepare for future master and slave, so we recommend that you enable this option when you deploy the master server. In addition, when being a master and slave, you also need the server-id of the master library and the slave library to be unique, and server-id is also a key recommendation parameter when making master and slave.

In addition, because our MySQL master library has been running for some time, there is already quite a lot of data in it, so we need to back up the data, and then synchronize the data from the backup nodes from the database, so as to maintain the data consistency between the master and slave, and it is best not to affect the normal operation of our business during the operation. Finally, we decided to use xtrabackup to back up the data, because we do not need trivial tables when backing up data with xtrabackup, but it is only limited to the databases of InnoDB engine and XtraDB engine. For the database of MyISAM engine, there are still trivial tables. It just so happens that our database engine uses InnoDB.

Procedure:

Step 1: deploy from the database server, preferably the database version is the same, the deployment process is omitted

Step 2: modify the configuration file

# the following configuration needs to be modified for the primary server. Server_id is not necessarily 82 and can be any number. For example, we can use the last bit [mysqld] datadir=/data/mysql of local IP. This option must be specified. Even if there is a default, you need to specify log_bin=mysql-binserver_id=10expire_logs_days=5 # to specify the number of days to retain the binlog file. If the hard disk space is large enough, you can not specify it. Or specify a little more days # the following is optional binlog-ignore-db=mysql,information_schema,performance_schema,sys # specify ignore out-of-sync database binlog-do-db=discuz,phpcms # specify database to be synchronized

Authorize a master-slave synchronization account on the master server

Mysql > grant replication slave on *. * to 'slave'@'%' identified by' 123qweASD'

Description: the above authorization commands can be changed according to their own needs, such as permissions, accessible database, user name, authorized client, password and other information

# the following is the configuration datadir=/data/mysql that needs to be modified from the server # this option must be specified Even if there is a default, you need to specify server_id = specify the optional replicate-do-db # specify the database replicate-ignore-db that needs to be synchronized # specify the database replicate-do-table that is out of sync # specify the table that needs to be synchronized replicate-ignore-table # specify Unsynchronized table replicate-wild-do-table # specifies the table that needs to be synchronized You can use wildcards, such as test.tables1%replicate-wild-ignore-table #, to specify tables that do not require synchronization, and you can use wildcard # to optimize synchronization latency, according to the project setting slave_parallel_workers = 16 # how many threads are used to replicate slave_parallel_type= logical_clock from the master

Step 3: install xtrabackup tools (both master and slave servers need to be installed)

Download xtrabackup suitable for your operating system version and MySQL version at the following connection

Xtrabackup download

After the download is complete, execute the following command to install

# this automatically installs the required dependency package yum-y install percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm

Step 4: back up the data

Note: need to use root user to execute, or use sudo permission to execute

Back up the entire library

Xtrabackup-user=root-password=123456-target-dir=/root/mysql_bak-S / tmp/mysql.sock-backup

-- user specifies the database access user name

-- password specifies the database access password. If the password has special characters, it needs to be enclosed in single quotation marks.

-- target-dir specifies the backup path, preferably absolute path

-- backup is used with the-- target-dir option

-S specifies the mysql.sock file location

Back up a single library

Xtrabackup-user=root-password=123456-databases=test1-target-dir=/root/mysql_bak-S / tmp/mysql.sock-backup

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

Back up multiple libraries

Xtrabackup-user=root-password=123456-databases= "test1 test2 test3"-target-dir=/root/mysql_bak-S / tmp/mysql.sock-backup

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

Back up the specified table of a library

Xtrabackup-user=root-password=123456-databases= "test1.tables1 test2.tables2 test3.tables3"-target-dir=/root/mysql_bak-S / tmp/mysql.sock-backup

-- databases backup tables use the library name. Table names are enclosed in double quotation marks if there are multiple tables, and different tables are separated by spaces

Step 5: copy the backup data to the slave server

Scp-pr / root/mysql_bak/* root@172.16.10.11:/data/mysql_slave

Step 6: restore the data to the slave server

Note: before restoring, you need to stop the slave database service and clear the slave server data directory, and back up the data to the actual place if you need it.

# stop service mysqld restart | | systemctl restart mysqld# backup original data mkdir / data/mysql_bakmv / data/mysql/* / data/mysql_bak

Restore operation

Xtrabackup-prepare-target-dir=/data/mysql_slavextrabackup-copy-back-target-dir=/data/mysql_slave

Step 7: start the database and enable the master and slave

# copy the original backup data back to the original directory. Note: do not overwrite the four ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 files. If there is no data from the library that needs to be copied back, you do not need to cp this operation cp-r / data/mysql_bak/* / data/mysqlchown-R mysql:mysql / data/mysqlservice mysqld restart | | systemctl restart mysqld

Execute the following command

Mysql > change master to master_host='172.16.10.10',master_port=3306,master_user='slave',master_password='123qweASD',master_log_file='mysql-bin.000011',master_log_pos=154

Master_host: the IP address of the master server

Master_port: Port of the master server

Master_user: the user name that the master server authorizes master-slave synchronization from the server

Master_password: the password that the master server authorizes master-slave synchronization from the slave server

Master_log_file: obtained from the file backed up by the master server: xtrabackup_info

Grep "binlog_pos" xtrabackup_info | awk-F "'{print $2}'

Master_log_pos: obtained from the file backed up by the master server: xtrabackup_info

Grep "binlog_pos" xtrabackup_info | awk-F "'{print $4}'

Execute the following command to enable master-slave synchronization

Mysql > start slave

Little knowledge: stop master-slave synchronization command is

# the following are extended knowledge commands. If you need to execute the previous command start slave;mysql > stop slave after execution

Step 8: test and check the master-slave status

Mysql > show slave status\ G

If you take the following two values as Yes, the master-slave synchronization is normal.

Slave_IO_Running: YesSlave_SQL_Running: Yes

Step 9: test

You can modify the library that needs to be synchronized, or whether the data of the table can be synchronized normally, or modify the data after the backup. At this time, there is no record of this modification in the backup. Check whether the data will be synchronized when master-slave synchronization is enabled.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.