MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves – Part Two

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.
About these ads

2 Responses to MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves – Part Two

  1. You use –start-from-beginning, which requires that server keeps _all_ binary log files forever, _and_ that slave runs _all_ transactions ever done on the server before it is ready. That does not seem very practical?

    It would be nice to see how to add a new GTID slave without –start-from-beginning. This does not seem to be mentioned anywhere (for example, the mysqlreplicate page has no mention of GTID).

    • Tony Darnell says:

      Kristian,

      You are correct. If you are creating a slave from a database where the binary logs have been purged, then using the mysqlreplicate utility isn’t your best option. In this case, you would need to copy the data from the master to the slave, and then start GTID. You could copy the data with mysqldump or MySQL Enterprise Backup. This link explains how to use GTID’s if you already have a master/slave configuration.

      Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: