One of my favorite features of MySQL is replication. Replication provides you with the ability to have MySQL automatically copy data from one MySQL instance to another. There are many benefits to using replication, but I just like having an extra copy of my data on another server in case the main server crashes. But if the master crashes, I can then use the MySQL mysqlfailover script to automatically failover from the master to theslave. (see my earlier post – Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication).
mysql一个被人很喜欢的特性就是主从复制，mysql可以从一个mysql实例复制到另外一个实例。使用主从复制有很多的好处，但我只想有一份拷贝防止主服务器崩溃，如果主服务器崩溃我可以使用mysql 故障恢复脚本自动从master恢复到slave。（另一篇文章- Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication）。
MySQL Replication automatically copies the data from the main database (master) to another database (slave). You can have multiple slaves pulling data from a single master, and you can have slaves replicating data off other slaves. If you are new to replication, check out the MySQL Replication FAQ page.
mysql主从备份自动从master复制数据到slave，你可以有多个slave从master复制数据，你也可以从其他的slave实例复制数据。如果你是第一次接触mysql主从备份，可以查看MySQL Replication FAQ 。
In this post, I will explain how to install or upgrade MySQL (by exporting and importing the data for a “fresh” upgrade), create a master and slave server, and start replication. If you have never installed MySQL or if you have never used replication, then this blog should be able to help you do this without too many headaches. One warning – this is a long post with a lot of details. And, this is not the only way to setup replication. This is just one way to do it. I will show you a different way in part two of this post. In order to try and keep this post as short as possible, I will not explain each command or feature, but instead I will post as many links as possible. This install was performed on a Macintosh with OS version 10.6.8 (for the master) and 10.8.1 (for the slave). This post should apply to most Unix installs, and it should work with Windows as well, with a few modifications.
在这篇文章里，我会解释如何安装和升级数据库，创建一个master和一个slave数据库，开始主从备份，如果你从来没有安装过mysql或者你从来没有使用主从备份，这边文章应该可以帮助你。一个提示-这是一篇有着很多细节的的文章并且这不是唯一的实现主从备份的方式，这只是一种方式。第二部分是另一种方式，为了是这篇文章尽可能的短，我不解释每个命令或者特性，但是我会贴出很多超链接，操作系统为Macintosh with OS version 10.6.8平台，这边文章里面的安装方式应该在大多数的unix平台都是适用的，并且在windows平台做适当的修改就可以运行。
I currently have one master database with three slaves attached. I will be upgrading from MySQL 5.6.8 to 5.6.9 and all of the servers are GTID-enabled. This post will be relevant if you are able to stop both the master and the slave during the upgrade process – or if you are installing a new master and a slave (without an existing database on either system).
我目前有一台master和3台slave。我将要把MySQL 5.6.8升级到5.6.9，并且所有的服务器都是GTID-enabled。你必须有能力停止所有的服务器除非你安装新的测试服务器。
When I upgrade my master server, I like to export the data and do a fresh install, and then re-import my data back into MySQL. Yes, there are ways to upgrade without doing having to export your data. But since I have a relatively small database (<100MB), I like starting with a new server and re-importing the data. This is just my preference.
当我升级master服务器，我想做一个全新的安装并且从新导入我的数据。很好，有很多不需要导出数据的升级方法，不过我的数据库很小(<100MB)，我喜欢安装一个新的服务器端程序。
The first thing that I will need to do is to export my data with mysqldump. I like to export my databases one at a time, in case I have problems with the import, then I can narrow the problem down to a specific database. I need a list of my databases, so from a MySQL prompt, I execute the show databases command.
第一件事是我需要使用 mysqldump导出我的数据，我一次只导出一个数据库，这样我可以缩小问题的范围（如果有的话），我需要一份数据库的清单，从mysql的客户端工具执行show databases 命令。
mysql> show databases; +--------------------+ | Database | +--------------------+ | addressbook | | comicbooks | | genealogy | | information_schema | | inventory | | mysql | | performance_schema | | scripting | | test | | twtr | | website | +--------------------+ 11 rows in set (0.92 sec)
I don’t want to export the four MySQL databases – information_schema, mysql, performance_schema or test – as these will be created in the new install. You are going to want to make sure that you don’t have any activity on your database before you export your data. From a mysql prompt (on the server you want to export data), you can use the FLUSH TABLES WITH READ LOCK command to prevent any additional inserts into the database and to allow all current transactions to be completed.
我不想要导出4个数据库- information_schema, mysql, performance_schema or test -因为他们会新安装的时候重新建立。你必须在导出数据库之前确定没有对数据库的读写操作，你可以使用FLUSH TABLES WITH READ LOCK 命令阻止新的写操作和所有的事物完成。
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)
I then export each database separately (my preference – you could do all of your databases at one time as well with the –all-databases option). In the command below, you will need to change DATABASE_NAME to the actual name of each of your databases:
然后分别导出每个数据库（只是我的偏好-你可以使用–all-databases一次导出所有的数据库），下面的命令你必须把 DATABASE_NAME必须改成实际的名字。
/usr/local/mysql/bin/mysqldump --user=root --password --quick --skip-opt --create-options \ --add-drop-database DATABASE_NAME > $HOME/mysql_backups/DATABASE_NAME.sql
Here is some information from the mysqldump page that explains each of the options that I used:
下面是我从mysqldump摘录的我使用过的选项信息：
--quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. I use this option in case I have problems importing the data, I easily edit the dump file and remove the bad data --skip-opt The --opt option is enabled by default, and --opt is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly. Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt. --create-options Include all MySQL-specific table options in the CREATE TABLE statements. --add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified.
You might want to read the mysqldump page to see which options you will want to use.
也许你想要看看 mysqldump的其他选项。
Once I have exported my data, I look at the export files to make sure that they were created. You might even want to open one of the smaller dump files (if the file isn’t too large) in a text-editor and just take a look to make sure everything looks good. If you used the same commands for each dump, then the smaller file will show you if the export is in the format you want.
一旦我导出了数据，我会检查一下。你可以文本编辑器打开一个小一点的文件看看内容，确保你导出了正确的内容，如果你导出的时候使用了相同的命令，小一点的文件应该就是你想要的格式。
If you have an existing MySQL database, you can also export the user and grant information so you can import this back into the new database. See my last blog post “Retrieving List of MySQL Users and Grants with Perl” to find out how to export your users and grants. You don’t have to use Perl to do this – you can manually use the commands explained in the post.
如果你应经有了一个已存在的数据库，你也可以导出用户和授权信息，这样你就可以导入到新的数据库，我的另一篇文章“Retrieving List of MySQL Users and Grants with Perl” 可以帮助你。
Now you can shutdown your MySQL instance.
现在你可以停止你的mysql实例了。
Once the database has shutdown, you can now install the new version of MySQL. In this case, I am installing version 5.6.9. If you are on Unix or Mac, and you are installing as root, you will need to change ownership to the mysql user of the files in your home MySQL directory after the install process.
一旦你停止了你的mysql实例，就可以安装新版本了。本文使用的是5.6.9。如果你用的unix或者mac，你需要把mysql 家目录所属赋给用户mysql。
After you install the new database, you will want to execute the mysql_install_db script. You can also refer to the post-installation procedures on the MySQL web site. Start MySQL, and run the script:
你可以通过 mysql_install_db 脚本来安装数据库，你也可以参考post-installation procedures 这篇文章，执行这个脚本：
root@macserver01: # ./scripts/mysql_install_db Installing MySQL system tables...OK Filling help tables...OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h macserver01 password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file ./my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as ./my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server
If you ran this script as root, you will need to change the ownership of the mysql-bin andmysql-bin.index files in the mysql data directory to the mysql Unix user.
如果以root来执行这个脚本，你需要把 mysql-bin和mysql-bin.index 文件的所属赋予mysql用户。
If you intend to use this server as a master server, you will need to edit the my.cnf (my.ini on Windows) file to make it ready to be a master server. The minimum you must change is to add these lines under the [mysqld] section of your my.cnf option file.
如果你想要使用这个服务器作为master服务器，有需要修改my.cnf (my.ini on Windows)文件，最小的修改是把下面的内容加入到 [mysqld] 项。
log-bin=mysql-bin server-id=1
The “server-id” must be unique to each server. I usually set my master server-id = 1. Check out the Setting the Replication Master Configuration page on the MySQL web site to make sure you have the correct settings for your server.
“server-id” 必须是唯一的，我通常把master服务器的server-id设置为1，参考文章 Setting the Replication Master Configuration 。
We will be using global transaction identifiers (GTID) for replication. GTID’s are a new replication feature as of MySQL 5.6.5. To enable GTID, you will need to add these lines under the [mysqld] section of your my.cnf option file.
我们将要使用 global transaction identifiers (GTID) 作为主从备份，GTID是一个 MySQL 5.6.5一个新的特性，你需要把下面的内容加入到my.cnf的[mysqld]项。
gtid_mode=ON enforce-gtid-consistency log-bin log-slave-updates binlog_format=mixed
Now you can start the MySQL server (if it isn’t already started). When you executed themysql_install_db script, it created the grant tables. You are going to want to change the root password and delete any anonymous accounts. See Securing the Initial MySQL Accountsfor specific information for your operating system.
现在你可以启动mysq-server start the MySQL server(如果没有启动)。 执行mysql_install_db 脚本可以创建 grant tables (用户授权表)。你可以修改root账户的密码和删除匿名账户，参考 Securing the Initial MySQL Accounts 。
An easy way to change the root password is to use mysqladmin from a command prompt:
命令行工具更方便 mysqladmin
$ ./bin/mysqladmin -u root password 'new-password'
Right after you change the root password, you will want to test the new root password by logging in with mysql as root at a Unix prompt:
就在你修改完root密码之后，你可以通过登录mysql命令行来验证。
root@macserver01: $ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2259 Server version: 5.6.9-rc-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Before we perform any more transactions, we need to make sure that we have GTID enabled. To see if GTID has been enabled, we can execute this statement from the mysql prompt:
在你执行任何transactions之前，你需要确定GTID被激活了。可以在mysql命令行执行：
mysql> show global variables like '%GTID%'; +--------------------------+-----------------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------------+ | enforce_gtid_consistency | ON | | gtid_executed | 82F20158-5A16-11E2-88F9-C4A801092ABB:1-26 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +--------------------------+-----------------------------------------------+ 5 rows in set (0.00 sec)
The variables enforce_gtid_consistency and gtid_mode should have the value of “ON”. The variable value for gtid_executed shows the UUID of the server, and the 1-26 shows that transactions one through 26 were executed. See GTID Concepts for more information about this value. Now that we know GTID has been enabled and our root password has been changed and confirmed, we can continue with deleting the anonymous accounts, creating our users and importing our data.
变量enforce_gtid_consistency 和 gtid_mode 应该是On。变量 gtid_executed的值是服务器的UUID，并且1-26的transactions已经被执行了的，参考 GTID Concepts 可以得到更多的信息。现在我们已经确定GTID被激活了，root密码也被修改了，我们可以删除匿名用户和创建新的用户，并且导入数据。
To find and delete the anonymous accounts, from a mysql prompt:
通过下面的命令行找到和删除匿名用户：
mysql> use mysql; Database changed mysql> SELECT user, host FROM user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | | macserver01.local | | root | macserver01.local | | | localhost | | root | localhost | +------+-----------------------+ 6 rows in set (0.00 sec)
The users that are blank are anonymous users. You can double-check the blank users with this statement:
用户是空白的是匿名用户，你可以多次验证下：
mysql> select user, host from user where user = ''; +------+-----------------------+ | user | host | +------+-----------------------+ | | VM-Mac-1081-128.local | | | localhost | +------+-----------------------+ 2 rows in set (0.00 sec)
You may now delete the blank users:
你现在可以删除匿名用户：
mysql> delete from user where user = ''; Query OK, 2 rows affected (0.00 sec)
These are the users that are remaining:
余下的还有：
mysql> select user, host from user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | root | VM-Mac-1081-128.local | | root | localhost | +------+-----------------------+ 4 rows in set (0.00 sec)
You can now create the users that you exported from your previous instance, or if this is a new install, you may create the users that you think you will need for this instance. If you exported the users, then you will want to remove the “CREATE USER” statement for the root users that match the user and host values above. If you had grants for these users that were different than the default grants, you can still execute the grant statements.
你可以导入上一个实例中导出的用户了，如果是全新的安装你可以创建你想要的用户。如果你要导入用户信息，你用该移除导入的sql里面关于root内容(防止重复)。
If you are installing MySQL for the first time on your master or if you did not have a replication user in your previous instance, you will need a replication user for replication. See Creating a User for Replication for more details, but here is a sample replication user creation statement:
如果你是第一次安装或者你没有专门的用户主从复制的用户，参考 Creating a User for Replication 获得更多信息，下面有一个简单的语句可以创建这个用户：
mysql> CREATE USER 'replicate'@'%.mydomain.com' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%.mydomain.com';
Now that our users have been created, we can import the data from our earlier export. If this is a new install, then you may skip this step. Before we import the data, let’s look at the master status. We will use this information later, so save the output to a text file.
现在用户已经创建了，我们可以把早先导出的数据导入，如果不需要可以跳过此步骤，在你导入数据之前，我们查看一下 master status，我们将要使用这些信息，把它保存你的文本文件里。
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 71046480
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-26
1 row in set (0.00 sec)
We can use the mysql program to import the data from the backups that we created earlier. You will need to execute this command for each database backup file:
可以使用程序mysql来导入早先导出的数据，你需要对每个数据库备份文件执行命令：
mysql -uroot -p DATABASE_NAME < $HOME/mysql_backups/DATABASE_NAME.sql
Once you have imported the data, you can check the master status to see how many transactions were executed. Since I exported my data with one insert statement per line, the total number of insert statements that I had in my import should be close to the number of transactions that were executed.
一旦完成，你可以查看master status，我导出的数据里面一行只有一个insert语句，所以所有数据库里面insert语句的行数和执行了的transactions数量是接近的。
mysql -uroot -p DATABASE_NAME show master status\G
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 71046480
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162551
1 row in set (0.00 sec)
The value of Executed_Gtid_Set contains the same type of information from the variablegtid_executed that we looked at previously. The value 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162551 contains the UUID of the server and shows that transactions one through 162551 have been executed on this new instance. Since we exported the original data with each data row on an individual INSERT line, we can now figure out how many rows of data we imported. Before we imported the data, the value of Executed_Gtid_Set was82F20158-5A16-11E2-88F9-C4A801092ABB:1-26 – so we executed 26 transactions before the data import. The difference in the total number of rows from the data import less the number of transactions that were executed earlier, minus the total number of any other statements (such as CREATE TABLE) should give us a count of the number of lines of data we imported. This step isn’t really necessary, but I like to do it just to make sure that I didn’t lose any data. And yes, it might be overkill.
Executed_Gtid_Set 的值包含的信息和我们之前gtid_executed 一样，82F20158-5A16-11E2-88F9-C4A801092ABB:1-162551包含server的UUID和1-162551的transactions已经在新的实例上执行了，既然我们导出的原始数据每一行都有一个insert语句，我们就可以知道导入了条数据。在我们导入数据之前Executed_Gtid_Set 的值为11E2-88F9-C4A801092ABB:1-26 –所以导入之前执行了26transactions ,总数减去其他的（比如create table）应该就是我们导入的条数，这一步骤不是必须的，但是我想验证一下我没有丢失任何数据。
We can do a line count for all of the data files that we imported earlier that contained an “INSERT” statement. This will give us a count of the total number of inserts from our import.
我们可以计算下导入了的所有文件的包含"INSERT"语句的行数。
$ ls -l $HOME/mysql_backups/*sql total 61832 -rw-r--r-- 1 root staff 1151006 Jan 9 00:12 addressbook.sql -rw-r--r-- 1 root staff 492652 Jan 8 23:11 comicbooks.sql -rw-r--r-- 1 root staff 27485322 Jan 8 23:11 genealogy.sql -rw-r--r-- 1 root staff 603943 Jan 8 23:11 inventory.sql -rw-r--r-- 1 root staff 779634 Jan 8 23:11 scripting.sql -rw-r--r-- 1 root staff 1077248 Jan 8 23:11 twtr.sql -rw-r--r-- 1 root staff 50643 Jan 8 23:11 website.sql $ grep INSERT *sql | wc -l 162444
Now we can get the total number of CREATE statements:
现在我们可以得到包含"CREATE"语句的条数：
root@macserver01: $ grep CREATE *sql | wc -l
81
The total number of transactions executed so far is 162551. If we subtract the number ofINSERT lines from the import (162444) and CREATE statements (81), we get the total of transactions that had taken place before the data import, which was 26. We can now confirm that all of our data was imported successfully. We can now install MySQL on the slave and start replication.
执行了的transactions总数是162551，162444(insert statement)+81(create statement) + 26(before) = 162551,我们可以确定导入数据成功，可以在slave上安装mysql，开始主从复制了。
For the slave, we will want to do the same steps for the install process as we did on the master, but we will stop at importing any data. Also, we will not have to create our additional users (with the exception of the replication user) and we will not have to import any data. Once we turn on the slave instance, the users will be replicated and the data we imported will be copied to the slave. Here are the steps:
对于slave，只要执行和master一样的安装步骤就可以了，但是我们不会导入数据，创建用户，一旦我们启动mysql，就可以复制我们之前导入的数据了，下面是步骤：
- Install MySQL version 5.6.9 (change ownership of the files in the mysql directory to mysql if you installed as root)
- Run the post-install script mysql_install_db (change ownership of the mysql-bin andmysql-bin.index files in the data directory if you installed as root)
- Change the root password and test it.
- You don’t have to remove the anonymous accounts, as the SQL statements that we performed on the master will also be executed on the slave.
- Create the replication user.
- Shutdown the mysql server.
- Edit the my.cnf options file (my.ini on Windows) and insert the GTID variables as shown earlier. But, the server-id value must be something other than the value from the master server. You can set this value to 2.
- Start the server
- Test to make sure GTID is enabled.
- Stop the slave by logging into mysql as root and executing “stop slave;”
- 安装mysql5.6.9修改mysql家目录所属为mysql用户
- 运行 mysql_install_db
- 修改root密码并且测试
- 你不需要删除匿名用户，master上执行的删除匿名用户的语句将在slave执行
- 创建主从复制的用户(使用master的主从复制用户就好，非必须)
- 停止mysql server
- 修改 my.cnf配置文件(windows 为my.ini)，插入GTID变量，就像之前做的一样，但是server-id必须和已有的服务器不同。
- 启动mysql server
- 确认GTID是激活了的
- 通过mysql命令行“stop slave;” 停止主从复制
mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec)
We can now provide the slave with the information on which master to use. Before GTID, you would have to tell mysql which binary log you wanted to use, and the position within that binary log. With GTID, you only need to set MASTER_AUTO_POSITION = 1“;
现在我们来提供slave的关于master服务器的信息，在GTID，你应该配置mysql使用那个二进制日志文件还有位置，使用GTID时你只需要设置MASTER_AUTO_POSITION = 1。
mysql> CHANGE MASTER TO
-> MASTER_HOST = '',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'replication_user_name',
-> MASTER_PASSWORD = 'replication_user_password',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.20 sec)
You will need to change the replication_user_name and replication_user_password to match the values you used when you created the replication user.
你将要修改 replication_user_name 和 replication_user_password 设置为你在master服务创建的replication用户的用户名和密码。
Before we turn on the slave, you may check the status of the slave:
我们可以在开启slave之前查看slave相关信息：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.2
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: WEB_SERVER_01-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 151
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2
1 row in set (0.00 sec)
We can check the values for Master_Host and Master_User to make sure they match our master server. Since the slave hasn’t been started yet, the value for Retrieved_Gtid_Set is blank – as we haven’t retrieved any data from the master. Once we start the slave, the value for Retrieved_Gtid_Set will show us how many transactions have been retrieved from the master. The value for Executed_Gtid_Set shows that we have executed two transactions on this new slave instance – changing the root password and creating the replication user. Now we can start the slave:
我们可以检查Master_Host和Master_User的值，既然slave还没启动，Retrieved_Gtid_Set的值为空(我们没有从master上接收到任何数据)。一旦我们start slave，从Retrieved_Gtid_Set值我们知道master接受了多少条数据，Executed_Gtid_Set值确定了我们在slave实例上执行了多少条transactions，我们可以启动slave了。
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
After a few moments, you can check on the slave status again.
过一会检查slave的状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1914
Relay_Log_File: WEB_SERVER_01-relay-bin.000007
Relay_Log_Pos: 1024
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Error 'Operation CREATE USER failed for 'replicate'@'%'' on query.
Default database: ''. Query: 'CREATE USER 'replicate'@'%'
IDENTIFIED BY PASSWORD '*BE1BDEC0AA74B4XCB07X943E70X28096CXA985F8''
Skip_Counter: 0
Exec_Master_Log_Pos: 814
Relay_Log_Space: 71051295
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1396
Last_SQL_Error: Error 'Operation CREATE USER failed for 'replicate'@'%'' on query.
Default database: ''. Query: 'CREATE USER 'replicate'@'%'
IDENTIFIED BY PASSWORD '*BE1BDEC0AA74B4DCB079943E70528096CCA985F8''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82f20158-5a16-11e2-88f9-c4a801092abb
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 130109 21:50:45
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3,
C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2
We can see from the value for Retrieved_Gtid_Set is 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562, which shows that we have already retrieved all of the transactions from the master. The Executed_Gtid_Set value of 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3shows that we have processed the first three transactions from the master, and the value of C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2 shows we have executed two transactions from the slave.
Retrieved_Gtid_Set 的值为 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562, 表示我们收到了所有的transactions ， Executed_Gtid_Set 的值为82F20158-5A16-11E2-88F9-C4A801092ABB:1-3 表示我们已经"processed"最开始的3个transactions ， C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2 表示已经"executed "2个transactions 。
The variable Last_Error shows that we also have an error because the CREATE USER statement for the replication user from the master database fails on the slave, as we have already created a replication user. I purposely created the same user to show you what happens when you have an error on the slave. In the above list of installation actions to do on the slave, you can skip “create replication user”.
Last_Error 表示最后的错误信息， CREATE USER 在slave上执行出错因为在slave已经有了一个相同的用户，我故意这么做是为了演示当你在slave执行出错的时候怎么处理。在上面你的安装步骤中是可以省略创建主从复制用户的。
To skip this error and continue with replication, you can set the SQL_SLAVE_SKIP_COUNTERto “1”, which tells the slave to skip one transaction. You must stop the slave, setSQL_SLAVE_SKIP_COUNTER = 1 and restart the slave.
你可以把变量SQL_SLAVE_SKIP_COUNTER 设置为"1"来跳过错误，这个语句可以让slave跳过一个transaction，你必须首先停止slave，setSQL_SLAVE_SKIP_COUNTER = 1 然后start slave就可以继续主从复制了。
mysql> stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;start slave; Query OK, 0 rows affected (0.16 sec)
Let’s look at the slave status again: (the output is truncated as we only need to look at the values for Retrieved_Gtid_Set and Executed_Gtid_Set)
我们再查看一下slave的信息：
mysql> show slave status\G
....
Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-15401,
C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-12
....
You can see that the value of the Executed_Gtid_Set has changed to 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-15401, which means that the slave is starting to process the transactions from the master, and is on transaction 15401. You can also see that transaction number four was skipped (when we executed the SET GLOBAL SQL_SLAVE_SKIP_COUNTER command). This was the transaction to create the replication slave user.
你可以看到Executed_Gtid_Set 的值已经为82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-15401,代表slave已经在处理第15401个transaction，你也可以到看到编号为4的transaction已经跳过了(我们执行的SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1)，这个transaction就是创建主从复制的用户。
You can check the slave status until you see that all of the transactions that were retrieved from the master have been completed on the slave:
你可以检查slave的状态知道接收到了所有的数据。
mysql> show slave status\G
....
Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-162562,
C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-12
....
You can go back to the master and see the master’s status:
你可以查看master的信息：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 1914
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562
1 row in set (0.00 sec)
We can now see that the value for Executed_Gtid_Set on the master is the same as the value on the slave (not including the transactions executed on the slave itself). So, the slave now has the same data as the master, and it is up to date and not lagging behind the master. If you have a busy master server, your slave might lag behind while it updates the records on the slave.
你可以看到Executed_Gtid_Set 的值在master和slave上是一样的，现在slave和master有相同的数据了，并且slave和master会同步，如果你的master服务器很忙，slave的数据可能会滞后于master。
396
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
