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

This article is about how to configure master-slave replication of mysql. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Database replication plays an important role for high availability and high performance promoters of the system.

1 main library configuration 1.1 my.cnf configuration:

Make the following basic configuration in the main library configuration file my.cnf:

Log-bin = mysql-bin / / binary log file name principal log-bin-index = mysql-bin.index / / binary log file index file server-id = 1 / / unique server ID, in order to maintain uniqueness, you can go to the tail of ip binlog-format = mixed / / to control how replication is based, there are statement-based (statement), line-based (row), mixed (mixed) * * Master / Slave libraries need to be consistent * * # sync_binlog=1 / / recommended configuration. Enable this option. Mysql will synchronize binary logs to disk each time before a transaction is committed, ensuring that events will not be lost in the event of a server crash.

All databases are replicated by default. If you need to specify a database, please refer to Section 7 (replication filtering).

For example, to specify db1 and db2 databases for master-slave replication: binlog-do-db = db1binlog-do-db = db21.2 to add a replication account:

Copy account addition and permission settings:

Mysql > grant replication slave, replicatin client on\ *.\ * to repl@'172.16.226.192' identified by 'repl123456'; / / where repl is the user name, repl123456 is the account password, and 172.16.226.168 is the address of the slave database. Mysql > flush privileges; / / complete the update of permissions without restarting the mysql service 2 standby library configuration

Make the following basic configuration in the slave configuration file my.cnf:

Relay-log = slave-relay-bin / / Relay log file name principal relay-log-index = slave-relay-bin.index / / Relay log file index file server-id = 2 / / unique server ID, which must be different from the main library # read_only = 1 / / restrict standby to read-only. Optional log_slave_updates = 1 / / controls whether synchronized data is added to your binlog after the relay log is executed. 1 indicates that skip_slave_start / / this option can prevent the slave from automatically starting replication after a crash. It is recommended to enable even if the recommended option is enabled, the standby may still be interrupted after a crash, because neither master.info nor intermediate log files are crash safe, so it is recommended to turn on the option: sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1

You can also filter the database or table to be synchronized, refer to the replication filtering section.

3 remote backup of database

Remote database backup can choose mysqldump (logical backup) for hot backup, but it will be slow when the amount of data is large. Xtrabackup (physical backup) can also provide hot backup for mysql database (innobackupex-1.5.1 is used here). Xtrabackup can back up innoDB and other databases online, which is fast and does not affect normal read and write. The whole library is backed up here.

3.1 create a backup account

Create a user backup (with minimum privileges) on the primary server for database backup.

Mysql > grant reload, lock tables, replication client on\ *.\ * to backup@'%' identified by 'backup123';mysql > flush privileges; / / complete permissions update 3.2 Database full backup without restarting the mysql service

The two steps of full backup and recovery preparation are completed on the main library server.

Innobackupex-1.5.1-- defaults-file=/etc/mysql/my.cnf-- user=backup-- password=backup123 / mysqlbackup--defaults-file: select the default configuration files-- user and-- password: prepare the user name and password for backup / mysqlbackup: target directory 3.3 for recovery

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. Such as the following command:

Innobackupex-1.5.1-- apply-log-- user=backup-- password=backup123 / mysqlbackup/2017-01-11 October 21-20-57 if executed correctly, the last lines of information output are usually as follows: xtrabackup: starting shutdown with innodb_fast_shutdown = 1120407 9:01:36 InnoDB: Starting shutdown...120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620120407 09:01:40 innobackupex: completed OK!

In the process of implementing preparation, innobackupex can also use the-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

3.4 data copy

Copy the database prepared on the master server to the slave server. (of course, you can also pack it and then copy it)

Scp-r / mysqlbackup/ copyer@192.168.1.192:/data/3.5 data recovery

Turn off the mysql service from the server before data recovery, and get the binary log files currently in use from the xtrabackup_binlog_info files in the backup folder, as well as the location of the binary log events up to this moment. If the datadir directory is not empty, you also need to empty the datadir directory.

The-- copy-back option of the innobackupex command is used to perform the restore operation, which performs the restore process by copying all data-related files to the mysql server datadir directory. Innobackupex uses backup-my.cnf to obtain information about the datadir directory (you can also specify the my.cnf directory through-- defaults-file, and make sure the datadir path is empty)

Innobackupex-1.5.1-- copy-back / mysqlbackup if executed correctly, the last lines of the output information are usually as follows: innobackupex: Starting to copy InnoDB log filesinnobackupex: in'/ backup/2012-04-07 July 08-17-03'innobackupex: back to original InnoDB log directory'/ mydata/data'innobackupex: Finished copying back files.120407 09:36:10 innobackupex: completed OK!

Make sure that "innobackupex: completed OK!" appears on the last line of the above information.

When the data is restored to the datadir directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the data file before starting mysqld. Such as:

Chown-R mysql:mysql / var/lib/mysql/4 Master-Slave connection 4.1 Open Slave Database service mysql start

If you fail to open mysql, you can find the reason for the failure by looking at the error log.

4.2 establish a master-slave connection

The slave library connects to the master library through the copy account: (slave must be in the stop state for the following connection to take effect)

Mysql > change master to master_host='192.168.1.208',master_user='repl',master_password='repl123456',master_log_file='mysql-bin.000001' (activity log obtained during backup), master_log_pos=0 (location of events in activity log obtained during backup)

Note: if the master has not been connected to the master-slave connection here, one possible reason is that the native machine is bound in the my.cnf configuration file, that is, bind-address = 127.0.0.1, all we have to do is comment it out, otherwise the external machine cannot be accessed.

Enable slave:

Mysql > start slave

Looking at the slave status, you can see that the IO thread and SQL thread are already on, and there are a lot of variables that represent the connection status of the slave library (these variables can also be used to set up master-slave monitoring), which are not covered here.

Mysql > show slave status;Slave_IO_Running: Yes / / indicates that the IO thread is running normally Slave_SQL_Running: Yes / / indicates that the SQL thread is running normally Seconds_Behind_Master: 0 / / indicates that when the network condition is good, the slave library can synchronize the master library in time, and the common monitoring commands mysql > show processlist\ G; / / check the database service thread status mysql > show master/slave status\ G; / / check the status of the master and standby database mysql > flush logs / force rotation (rotate) binary log to get a complete binary log file mysql > show binlog events in 'specify binary log file name' from (display from specified location) limit (number of events to be displayed)\ G; / / View event mysql > show binary logs; / / display all binlogsmysql > reset master; / / Delete all binary log files and clear the index file mysql > reset slave / / Delete all files used for replication on slave and restart mysql > show slave hosts; / / View the slave library information owned by the master library

5 there is a large delay from the library

If you find a large delay from the library, you need to find the reason for the large delay. The parameter innodb_flush_log_at_trx_commit has a great influence on the writing efficiency of mysql, and there are three values:

0: every second, write the data of the transaction log cache to the log file, and refresh the data of the log file to disk; 1: when each transaction commits, write the transaction log from the cache to the log file and refresh the data of the log file to disk; 2: when each transaction commits, write the transaction log data from the cache to the log file Refresh the log file every other second, not necessarily to disk, but depending on the scheduling of the operating system

The IO of 1 is the most expensive, although the consistency and integrity are the best, but the write efficiency is the lowest, which is also the reason for the large slave latency (it may be better if the server configuration is higher). Mysql write performance is good when 0 is taken, but if the mysqld process crashes, it usually results in log loss for the last 1s. The write performance when taking 2 is also very good, each transaction commit will write to the log file, but will not immediately write to disk, the log file will be written to disk once every second. At this point, if the mysqld process crashes, the data will not be lost because the log has been written to the system cache; in the case of an operating system crash, it will usually result in the loss of the log for the last 1s.

6 mixed mode replication

Normally use statement-based replication, while switching to row-based replication for unsafe statements. There are mainly the following situations:

This statement calls: UUID function user-defined function CURRENT_USER or USER function LOAD_FILE function A statement updates two or more table statements containing AUTO_INCREMENT columns at the same time using the server variable storage engine does not allow statement-based replication, for example, mysql cluster engine 7 replication filtering

Sometimes we don't need to copy all the libraries in the database, or we don't want to copy some tables in the specified library, so we need to filter the replication to achieve a more reasonable replication effect.

1. Based on the master**binlog-do-db=mysql**: master library, only changes in the specified library (mysql) are recorded in the binary log. * * the binlog-ignore-db=mysql**: master library cancels recording changes in the specified library (mysql) to the binary log. two。 Based on slave

Filtering against the database:

* * the replicate-do-db=mysql**: slave library only reproduces the changes that have occurred in the specified library (mysql). * * replicate-ignore-db=mysql**: cancels the library to reproduce the changes that have occurred in the specified library (mysql). Filtering against tables: * * replicate-wild_do-table=mysql.learn**: from the library only reproduces the changes in the specified table (learn) in the specified library (mysql). * * replicate-wild_ignore-table=mysql.learn**: cancels from the library to reproduce the changes in the specified table (learn) in the specified library (mysql).

At first glance, there is no problem with the above replication and filtering methods, but in fact, there are still places that need to be paid attention to. Because the effect of these filtering methods has something to do with the way of replication. If it is statement-based replication, it should be noted that binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db are related to cross-libraries (such as inside and outside the use library).

8 Log cleanup brute force cleanup: (without master-slave replication) 1. Restart the mysql server to turn off the recording of bin logs. 2. Clean up conditions through the reset master command.

If there is a master-slave replication relationship, you should use purge to clean up the bin logs, with the following syntax:

Purge {master | binary} logs to 'log_name'purge {master | binary} logs before' date'

The user deletes all binary logs listed in the log index before the specified log or date, and these logs are also deleted from the list of log index files.

Eg.purge master logs to 'mysql-bin.000005';purge master logs before' 2014-08-30 00 purge master logs before date_sub / clear the log before the specified date (now (), Interval 3 log); clean up the log three days ago regularly

Parameter: expire_logs_days

Description: the number of days that binary logs are automatically deleted / expired. The default value is' 0expired, that is, there is no expired

Example: expire_logs_days = 5, indicating that the valid time of the log is 5 days

When will the expired log be deleted?

Expired logs are automatically deleted every time log flush is performed.

When will log flush be triggered?

1, restart 2, the size of the binlog file has reached the maximum limit 3, manually execute the flush logs command thank you for reading! On the master-slave replication of mysql how to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.