This post is part two of MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves. In this post, I will be showing you how to use the MySQL Utility Script mysqlreplicate to create a new replication slave off a master database.
This is also the fourth in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts. You may want to read the first half of this post to understand how MySQL Workbench Utilities work and how you access the scripts. These scripts were written by Chuck Bell (a MySQL employee) and are available as stand-alone scripts (see Chuck’s blog for more information) or as part of the MySQL Workbench utility.
The mysqlreplicate “utility permits an administrator to start replication from one server (the master) to another (the slave). The user provides login information for the slave and connection information for connecting to the master.” (From: http://dev.mysql.com/doc/workbench/en/mysqlreplicate.html)
In the first post, I showed you how to create a slave off a master. The mysqlreplicate utility takes care of the manual steps for you, and makes it easy to create a slave database. I am not going to rewrite a lot of the details from the first post, so you may refer to that post for more information.
The first step is to install the MySQL database on a new server that is going to be your slave server. In this example, I am installing MySQL version 5.6.9 (Community Edition) on a Macintosh with OS 10.6.8. This tutorials should work for any platform, with just a few tweaks for Windows users.
After the install, I will need to change the permissions on my mysql directory, since I installed the database as root:
# ls -l total 8 lrwxr-xr-x 1 root wheel 29 Jan 25 11:50 mysql -> mysql-5.6.9-rc-osx10.7-x86_64 drwxr-xr-x 18 root wheel 612 Jan 25 11:51 mysql-5.6.9-rc-osx10.7-x86_64 # chown -R _mysql mysql* # ls -l total 8 lrwxr-xr-x 1 _mysql wheel 29 Jan 25 11:50 mysql -> mysql-5.6.9-rc-osx10.7-x86_64 drwxr-xr-x 18 _mysql wheel 612 Jan 25 11:51 mysql-5.6.9-rc-osx10.7-x86_64 |
Next, I will run the mysql_install_db script from my mysql home directory (/usr/local/mysql) – /usr/local/mysql:
root# cd /usr/local/mysql root# ls -l scripts total 72 -rwxr-xr-x 1 _mysql wheel 33018 Nov 22 10:28 mysql_install_db root# ./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 VM-Mac-1081-122.local 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 |
Again, since I ran this as root, I will need to change permissions on a few files in the mysql directory to the mysql user. There are some files in the data directory that are owned by root, but I can just do a change ownership of the entire mysql directory.
I will now need to start mysql, and then change the root password. I can change the password with the mysqladmin client application:
./bin/mysqladmin -u root password 'newpassword' |
I then check to make sure that my password change worked. I can do this by logging into mysql and I will also list the users in the database:
root# mysql -uroot -p mysql> use mysql; Database changed mysql> select user, host, password from users; ERROR 1146 (42S02): Table 'mysql.users' doesn't exist mysql> select user, host, password from user; +---------+-----------------------+-------------------------------------------+ | user | host | password | +---------+-----------------------+-------------------------------------------+ | root | localhost | *8FF7274XXF360A5BB33835F544D6617707C23968 | | root | VM-Mac-1081-122.local | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | VM-Mac-1081-122.local | | +---------+-----------------------+-------------------------------------------+ 6 rows in set (0.00 sec) |
You can see that mysqladmin only changed the root password for the localhost (the rest of the password fields were blank). I can change the rest of the root passwords once I am in mysql:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
|
I will also want to delete the anonymous users.
mysql> delete from user where user = ''; Query OK, 2 rows affected (0.00 sec) |
Prior to starting this server as a slave, we can see that there are only the four mysql databases:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) |
And since we haven’t started it as a slave, the SHOW SLAVE STATUS is empty:
mysql> show slave status\G Empty set (0.00 sec) |
I prefer to run all of my scripts with a user named “scripts” versus using the root mysql user, so I will create the scripts user:
CREATE USER 'scripts'@'192.168.1.122' IDENTIFIED BY 'scripts123'; GRANT ALL PRIVILEGES ON *.* TO 'scripts'@'192.168.1.122' WITH GRANT OPTION; |
You can just use the root user if you want. But the mysqlreplicate script does not allow for special characters in the password field, so you will want to use a password without any special characters.
We will need to create a replication use on the master for the slave, which is at 192.168.1.2.
CREATE USER 'replicate'@'192.168.1.122' IDENTIFIED BY 'replicate123'; GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.122'; |
Now we are ready to run the mysqlreplicate script. You will want to review the mysqlreplicate man page for the variables and their use:
$ mysqlreplicate --master=scripts:scripts123@192.168.1.2:3306 \ --slave=scripts:scripts123@192.168.1.122:3306 \ --rpl-user=replicate:replicate123 --start-from-beginning -vvv # master on 192.168.1.2: ... connected. # slave on 192.168.1.122: ... connected. # master id = 1 # slave id = 5 # Checking InnoDB statistics for type and version conflicts. # Checking storage engines... # Checking for binary logging on master... # Setting up replication... # Connecting slave to master... # CHANGE MASTER TO MASTER_HOST = '192.168.1.2', MASTER_USER = 'replicate', MASTER_PASSWORD = 'replicate123', MASTER_PORT = 3306 # Starting slave from the beginning... # status: Queueing master event to the relay log # Waiting for slave to synchronize with master # status: Queueing master event to the relay log # Waiting for slave to synchronize with master # status: Waiting for master to send event # ...done. |
That’s it! The mysqlreplicate utility has done all of the steps necessary to start the slave. After a few minutes, we can now look at the slave status:
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: 137301
Relay_Log_File: WEB_SERVER_01-relay-bin.000012
Relay_Log_Pos: 354
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 137301
Relay_Log_Space: 769
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: 0
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: 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: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162868
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162868,
B7DB3608-6729-11E2-9E3C-BDE2366761B1:1-4
1 row in set (0.00 sec)
mysql>
|
If your slave hasn’t caught up with the master yet, the values for Retrieved_Gtid_Set and Executed_Gtid_Set will not match. It only took my slave a few minutes to catch up to my master, but your situation will be different. We can now compare the status of the Executed_Gtid_Set to the master, and it matches:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 137301
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162868
1 row in set (0.00 sec)
|
If your master database was active with write transactions, then master’s Executed_Gtid_Set value may be higher and not match the slave. But your slave is now ready for use.
 |
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. |
