Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. Advantages of replication in MySQL include: * Scale-out solutions - 读写分离，一主（master）多从（slaves），主写（insert,update）从读（read） * Data security - 从库的数据随便搞。 * Analytics - 利用从库的数据进行分析。 * Long-distance data distribution - 通过数据文件进行同步，无需与远程的主库建立持久连接。 [TOC] ## Replication methods MySQL 5.7 supports different methods of replication. * The traditional method is based on replicating events from the master's binary log, and requires the log files and positions in them to be synchronized between master and slave. * The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied on the slave. ### Using binary log file position based replication Section 16.1, “Configuring Replication”. The master and each slave must be configured with a unique ID (using the[`server-id`](https://dev.mysql.com/doc/refman/5.7/en/replication-options.html#option_mysqld_server-id)option). In addition, each slave must be configured with information about the master host name, log file name, and position within that file. These details can be controlled from within a MySQL session using the[`CHANGE MASTER TO`](https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html "13.4.2.1 CHANGE MASTER TO Syntax")statement on the slave. The details are stored within the slave's master info repository, which can be either a file or a table. * master writes updates and changes as“events”to the binary log. configure: ~~~ini [mysqld] log-bin=mysql-bin server-id=1 // For the greatest possible durability and consistency in a replication setup using InnoDB with transactions innodb_flush_log_at_trx_commit=1 sync_binlog=1 ~~~ >[info] Note > * if you omit `server-id` , the master refuses any connections from slaves. > * Ensure that the `skip-networking` option is not enabled on your replication master. If networking has been disabled, the slave cannot communicate with the master and replication fails. * slave receives a copy of the entire contents of the binary log and to execute the events in the binary log. If required, you can configure the slave to process only events that apply to particular databases or tables. It is possible for slaves to be disconnected, reconnect and then resume processing. Certain steps within the setup process require the `SUPER` privilege. configure: ~~~ini ~~~ After making the changes, restart the server. `CHANGE MASTER TO` statement host name,log file name,position,hostport,user,password The details are stored within the slave's master info repository, which can be either a file or a table. * copying data between the master and slaves master make datasnapshot? using `MyISAM`, you must 1. stop processing statements on the master to obtain a read-lock, 2. obtain its current binary log coordinates and dump its data, before permitting the master to continue executing statements. If you do not stop the execution of statements, the data dump and the master status information will not match, resulting in inconsistent or corrupted databases on the slaves. using `InnoDb`, you do not need a read-lock and a transaction that is long enough to transfer the data snapshot is sufficient. slave import datasnapshot? To set up replication in 3 scenarios: * a fresh installation of a master and slaves that contain no data, * a new master using the data from an existing MySQL server, * add replication slaves to an existing replication environment ### GTID-based replication Section 16.1.3, “Replication with Global Transaction Identifiers”. ## Replication synchronization ### Semisynchronous replication In MySQL 5.7, semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the master blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction; see Section 16.3.9, “Semisynchronous Replication”. ### Delayed replication MySQL 5.7 also supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time; see Section 16.3.10, “Delayed Replication”. ## Replication setting up solutions the best method to use depends on the presence of data and the engine types you are using. For more information on the available options, see Section 16.1.2, “Setting Up Binary Log File Position Based Replication”. * **Master** 1. enable `binary logging` and configuer a unique `server ID`. This might require a server restart. `binary log`: the basis for replicating changes from the master to its slaves. `server ID`: is used to identify individual servers within a replication group, and must be a positive integer between 1 and (2E32)−1. How you organize and select the numbers is your choice. To configure the binary log and server ID options： * shut down the MySQL server * edit the`my.cnf`or`my.ini`file. Within the`[mysqld]`section of the configuration file, add the`log-bin`and`server-id`options. ~~~ [mysqld] log-bin=mysql-bin server-id=1 ;二进制日志的格式 binlog_format=row ;防止发生服务器崩溃时导致复制破坏。 sync_binlog=1 innodb_flush_log_at_trx_commit=1 ~~~ * restart the server. >[info] Note The following options have an impact on this procedure: > * if you omit[`server-id`](https://dev.mysql.com/doc/refman/5.7/en/replication-options.html#option_mysqld_server-id)(or set it explicitly to its default value of 0), the master refuses any connections from slaves. > * For the greatest possible durability and consistency in a replication setup using[`InnoDB`](https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html "Chapter 14 The InnoDB Storage Engine")with transactions, you should use`innodb_flush_log_at_trx_commit=1`and`sync_binlog=1`in the master`my.cnf`file. > * Ensure that the[`skip-networking`](https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_skip-networking)option is not enabled on your replication master. If networking has been disabled, the slave cannot communicate with the master and replication fails. 2. record the master's current position in the binary log. You need this information when configuring the slave so that the slave knows where within the binary log to start executing events. To obtain the master binary log coordinates, follow these steps: ~~~ mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; ~~~ | File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | | --- | --- | --- | --- | | mysql\-bin.000003 | 73 | test | manual,mysql | the binary log file is`mysql-bin.000003`and the position is `73`. They represent the replication coordinates at which the slave should begin processing new updates from the master. If the master has been running previously without binary logging enabled, the log file name and position values displayed by[`SHOW MASTER STATUS`](https://dev.mysql.com/doc/refman/5.7/en/show-master-status.html "13.7.5.23 SHOW MASTER STATUS Syntax")or [**mysqldump --master-data**](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html "4.5.4 mysqldump — A Database Backup Program") are empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string (`''`) and`4`. * shut down the master to create a data snapshot, the master creates a new binary log file on restart. The master binary log coordinates where the slave must start the replication process are therefore the start of that new file, which is the next binary log file on the master following after the files that are listed in the copied binary log index file. 3. If you already have data on the master and want to use it to synchronize the slave, you need to create a data snapshot to copy the data to the slave.The storage engine you are using has an impact on how you create the snapshot: * using `MyISAM`: stop processing statements on the master to obtain a read-lock, then obtain its current binary log coordinates and dump its data, before permitting the master to continue executing statements. If you do not stop the execution of statements, the data dump and the master status information will not match, resulting in inconsistent or corrupted databases on the slaves. * using `InnoDB`: do not need a read-lock and a transaction that is long enough to transfer the data snapshot is sufficient. * **Slave** 1. configure a unique server ID. This might require a server restart. ~~~ [mysqld] server-id=2 ;防止没有super权限的用户在slave上进行写 read-only=1 ;防止在启动slave数据库时，自动启动复制线程。以后需要手动start slave来启动复制线程。 skip-slave-start=1 ~~~ If you are setting up multiple slaves, each one must have a unique[`server-id`](https://dev.mysql.com/doc/refman/5.7/en/replication-options.html#option_mysqld_server-id)value that differs from that of the master and from any of the other slaves. >[info] Note > If you omit[`server-id`](https://dev.mysql.com/doc/refman/5.7/en/replication-options.html#option_mysqld_server-id)(or set it explicitly to its default value of 0), the slave refuses to connect to a master. > if you enable binary logging on the slave, you can use the slave's binary log for data backups and crash recovery, and also use the slave as part of a more complex replication topology. For example, where this slave then acts as a master to other slaves. 2. Optionally, create a separate user for your slaves to use during authentication with the master when reading the binary log for replication. 3. Configure the slave with settings for connecting to the master, such as the host name, login credentials, and binary log file name and position. ~~~ mysql> CHANGE MASTER TO -> MASTER_HOST='master_host_name', -> MASTER_USER='replication_user_name', -> MASTER_PASSWORD='replication_password', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position; mysql> SHOW SLAVE STATUS\G ~~~ After configuring the basic options, select your scenario: * To set up replication for a fresh installation of a master and slaves that contain no data, see[Section 16.1.2.5.3, “Setting Up Replication between a New Master and Slaves”](https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html#replication-howto-newservers "16.1.2.5.3 Setting Up Replication between a New Master and Slaves"). * To set up replication of a new master using the data from an existing MySQL server, see[Section 16.1.2.5.4, “Setting Up Replication with Existing Data”](https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html#replication-howto-existingdata "16.1.2.5.4 Setting Up Replication with Existing Data"). * To add replication slaves to an existing replication environment, see[Section 16.1.2.6, “Adding Slaves to a Replication Environment”](https://dev.mysql.com/doc/refman/5.7/en/replication-howto-additionalslaves.html "16.1.2.6 Adding Slaves to a Replication Environment"). add: Before administering MySQL replication servers, read this entire chapter and try all statements mentioned in[Section 13.4.1, “SQL Statements for Controlling Master Servers”](https://dev.mysql.com/doc/refman/5.7/en/replication-master-sql.html "13.4.1 SQL Statements for Controlling Master Servers"), and[Section 13.4.2, “SQL Statements for Controlling Slave Servers”](https://dev.mysql.com/doc/refman/5.7/en/replication-slave-sql.html "13.4.2 SQL Statements for Controlling Slave Servers"). Also familiarize yourself with the replication startup options described in[Section 16.1.6, “Replication and Binary Logging Options and Variables”](https://dev.mysql.com/doc/refman/5.7/en/replication-options.html "16.1.6 Replication and Binary Logging Options and Variables"). ### Method for Data Snapshots [[官方文档]]([https://dev.mysql.com/doc/refman/5.7/en/replication-snapshot-method.html](https://dev.mysql.com/doc/refman/5.7/en/replication-snapshot-method.html)) * Use the[`mysqldump`](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html "4.5.4 mysqldump — A Database Backup Program")tool. create a dump of all the databases you want to replicate. This is the recommended method, especially when using `InnoDB`. ~~~ shell> mysqldump --all-databases --master-data > dbdump.db windowsPowershell> mysqldump -uipmsv3 -p --databases ipmsv3 mqttl wordpress --master-data --result-file=c:\tmp\dbdump.db ~~~ dumps all databases to a file named`dbdump.db`, and includes the[`--master-data`](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data)option which automatically appends the `CHANGE MASTER TO` statement required on the slave to start the replication process. >[info] Note > * If you do not use[`--master-data`](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data), then it is necessary to lock all tables in a separate session manually. See[Section 16.1.2.3, “Obtaining the Replication Master Binary Log Coordinates”](https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterstatus.html "16.1.2.3 Obtaining the Replication Master Binary Log Coordinates"). > * Exclude all the tables in the database using `--ignore-table` option. > * Name only those databases which you want dumped using the `--databases` option. > * To import the data, either copy the dump file to the slave, or access the file from the master when connecting remotely to the slave. >[info] Note > * A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding: ~~~terminal shell> mysqldump [options] > dump.sql ~~~ >[info] * However, UTF-16 is not permitted as a connection character set, so the dump file will not load correctly. To work around this issue, use the`--result-file`option, which creates the output in ASCII format: ~~~terminal shell> mysqldump [options] --result-file=dump.sql ~~~ * copy the raw data files to a slave.If your database is stored in binary portable files,This can be more efficient than using `mysqldump` and importing the file on each slave, because it skips the overhead of updating indexes as the`INSERT`statements are replayed. With storage engines such as `InnoDB` this is not recommended. ### import the data * copy the dump file to the slave, * access the file from the master when connecting remotely to the slave. 执行sql脚本： ~~~ mysql> source /tmp/dbdump.db ~~~ ## Replication format Events in the binary log are recorded using a number of formats.There are two core types of replication format, * Statement Based Replication (SBR), which replicates entire SQL statements, and * Row Based Replication (RBR), which replicates only the changed rows. * You can also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 16.2.1, “Replication Formats”. ### Statement Based Replication (SBR) ### Row Based Replication (RBR) ## Replication controll Replication is controlled through a number of different options and variables. For more information, see Section 16.1.6, “Replication and Binary Logging Options and Variables”. ## Replication Solutions You can use replication to solve a number of different problems, including performance, supporting the backup of different databases, and as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 16.3, “Replication Solutions”. ## Replication Implementation For detailed information on the implementation of replication, how replication works, the process and contents of the binary log, background threads and the rules used to decide how statements are recorded and replicated, see Section 16.2, “Replication Implementation”. ## Replication Notes and Tips For notes and tips on how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and potential problems and their resolution, see Section 16.4, “Replication Notes and Tips”. For answers to some questions often asked by those who are new to MySQL Replication, see Section A.13, “MySQL 5.7 FAQ: Replication”. ## Using Option Files [[官方文档]]([https://dev.mysql.com/doc/refman/5.7/en/option-files.html](https://dev.mysql.com/doc/refman/5.7/en/option-files.html)) * **Option Files Read on Windows Systems** | File Name | Purpose | | --- | --- | | `` `%WINDIR%`\my.ini``,`` `%WINDIR%`\my.cnf`` | Global options | | `C:\my.ini`,`C:\my.cnf` | Global options | | ``*`BASEDIR`*\my.ini``,``*`BASEDIR`*\my.cnf`` | Global options | | `defaults-extra-file` | The file specified with[`--defaults-extra-file`](https://dev.mysql.com/doc/refman/5.7/en/option-file-options.html#option_general_defaults-extra-file), if any | | `` `%APPDATA%`\MySQL\.mylogin.cnf`` | Login path options (clients only) | * **Option Files Read on Unix and Unix-Like Systems** | File Name | Purpose | | --- | --- | | `/etc/my.cnf` | Global options | | `/etc/mysql/my.cnf` | Global options | | ``*`SYSCONFDIR`*/my.cnf`` | Global options | | `$MYSQL_HOME/my.cnf` | Server-specific options (server only) | | `defaults-extra-file` | The file specified with[`--defaults-extra-file`](https://dev.mysql.com/doc/refman/5.7/en/option-file-options.html#option_general_defaults-extra-file), if any | | `~/.my.cnf` | User-specific options | | `~/.mylogin.cnf` | User-specific login path options (clients only) | ## 备忘 ### 1. 远程连接出现Error 1130问题 解决该问题有以下两个方法 1. 改表法 可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从”localhost”改称”%” ~~~ shell> mysql -u root -p mysql>use mysql; mysql>update user set host = '%' where user = 'root'; mysql>flush privileges; mysql>select host, user from user ~~~ 2. 授权法 例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。 ~~~ GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; ~~~ 如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码 ~~~ GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; ~~~ ## 2. ERROR 1146 (42S02): Table 'performance\_schema.session\_variables' doesn't exist 2016年11月28日 23:23:17[贝伦酱](https://me.csdn.net/belen_xue)阅读数：2226 之前mysql升级后使用show variables出现该错误，需要进行mysql\_upgrade操作进行修复。 1）首先退出mysql 2）在命令行界面输入： mysql_upgrade -uroot -p --force 3）然后重启mysql即可 windows重启mysql命令是: net stop mysql//关闭mysql net start mysql//启动mysql