Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication

In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as MySQL replication is very reliable and works really well.

A few months ago, I started writing about the MySQL Utilities. If you haven’t heard about the MySQL Utilities:

“MySQL Utilities is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands, and bundles them so they can be used to perform macro operations with a single command. MySQL Utilities may be installed via MySQL Workbench, or as a standalone package. The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python 2.x greater than 2.6.” (from the introduction to MySQL Utilities page)

In order to reduce my paranoia (I will never be able to eliminate it), I can simply use the mysqldbcompare utility. The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.” (from: mysqldbcompare — Compare Two Databases and Identify Differences)

You don’t have to use mysqldcompare to compare two databases in replication. You may compare any two databases, even if they are on the same server. But for this post, I will be comparing two databases on two separate servers in a master/slave replication topology.

The utility is fairly easy to use. You just identify the two servers and which databases you want to compare. You will need to refer to my earlier post on using the MySQL Utilities for more information on how to execute the scripts.

I will be comparing a database that is on my master server (at 192.168.1.2) and the same database that is on one of the slaves (at 192.168.1.122) connected to that master. Instead of using root to execute the scripts, I create and use a MySQL user named “scripts” to use when I run a script. The syntax for mysqldbcompare is fairly easy, and you can refer to the mysqldbcompare man page for more of the commands and their usage:

I need to specify the servers, user name and passwords:

--server1=scripts:scripts999@192.168.1.2 
--server2=scripts:scripts999@192.168.1.122 

The name of the databases to compare (database_server1:database_server2):

cbgc:cbgc 

Do not stop the script at the first difference that is found. Process all objects.

--run-all-tests 

Specify the server to show transformations to match the other server.

--changes-for=server2 

Specify the difference display format. Permitted format values are unified, context, differ, and sql. The default is unified.

--difftype=sql

Now that I have decided on which options to use, I can run the scripts from within the MySQL Workbench Utilities shell:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 \
  --server2=scripts:scripts999@192.168.1.122 \
  cbgc:cbgc --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.122: ... connected.
# Checking databases cbgc on server1 and cbgc on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Activity_Affiliate                      pass    pass    pass    
# TABLE     FedEx_2nd_Day                           pass    pass    pass    
# TABLE     FedEx_Express_Saver                     pass    pass    pass    
# TABLE     FedEx_Ground                            pass    pass    pass    
# TABLE     FedEx_Home                              pass    pass    pass    
# TABLE     FedEx_Priority_Overnight                pass    pass    pass    
# TABLE     FedEx_Standard_Overnight                pass    pass    pass    
# TABLE     Orders                                  pass    pass    pass    
# TABLE     USPS                                    pass    pass    pass    
# TABLE     activity                                pass    pass    pass    
# TABLE     comics                                  pass    pass    pass    
# TABLE     coupons                                 pass    pass    pass    
# TABLE     customer                                pass    pass    pass    
# TABLE     giftcert                                pass    pass    pass   

Databases are consistent.
#
# ...done

The output shows that my databases are consistent. I have a rather small database, and I re-executed the script again with the “time” command, and here are the time results:

real	0m4.519s
user	0m0.429s
sys	0m0.068s

It took about 4.5 seconds to execute on my database which is about 25 megabytes in size. Obviously, the time will increase relative to the complexity and size of your database. And the time will increase relative to the number of differences that the script finds.

In order to show you what happens when the databases are not in sync, I will now stop the slave database (at 192.168.1.122), and then add a line to one of the tables in the master database (at 192.168.1.2), and re-run the script (while the slave is still stopped).

On the slave server:

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

On the master server:

mysql> insert into `cbgc`.`activity` ( `Customer_Serial_Number`, `Customer_Activity_Action`) \
          values ( '1201201', 'Test Visit');
Query OK, 1 row affected (0.96 sec)

Now I can run the mysqldbcompare script again. The slave is still turned off:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 \
  --server2=scripts:scripts999@192.168.1.122 cbgc:cbgc \ 
  --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.122: ... connected.
# Checking databases cbgc on server1 and cbgc on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Activity_Affiliate                      pass    pass    pass    
# TABLE     FedEx_2nd_Day                           pass    pass    pass    
# TABLE     FedEx_Express_Saver                     pass    pass    pass    
# TABLE     FedEx_Ground                            pass    pass    pass    
# TABLE     FedEx_Home                              pass    pass    pass    
# TABLE     FedEx_Priority_Overnight                pass    pass    pass    
# TABLE     FedEx_Standard_Overnight                pass    pass    pass    
# TABLE     Orders                                  pass    pass    pass    
# TABLE     USPS                                    pass    pass    pass    
# TABLE     activity                                FAIL    FAIL    FAIL    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE cbgc.activity 
  DROP PRIMARY KEY, 
  ADD PRIMARY KEY(serial_id), 
AUTO_INCREMENT=7542;

# Row counts are not the same among cbgc.activity and cbgc.activity.
#
# Transformation for --changes-for=server2:
#

INSERT INTO cbgc.activity (serial_id, Customer_Serial_Number, Customer_Activity_Action, 
Customer_Activity_Date_Time, Customer_Activity_Info, Notes, HTTP_REFERER) 
VALUES('7541', '1201201', 'Test Visit', NULL, NULL, NULL, NULL);


# TABLE     comics                                  pass    pass    pass    
# TABLE     coupons                                 pass    pass    pass    
# TABLE     customer                                pass    pass    pass    
# TABLE     giftcert                                pass    pass    pass   

# Database consistency check failed.
#
# ...done

The script alerted me to two issues. It tells me that I have one row of data missing (from the insert statement), but it also notices that my auto-increment on the activity database needs to be updated as well. If I simply run the insert statement, my auto-increment value will be incorrect. So, I need to run the auto-increment change first, and then I can execute the insert statement.

There are other choices of output that you can use besides sql – unified, context and differ. You can try each one and see which one will fit your needs. I prefer the sql output.

Since I stopped the slave, I can just start it again and it will catch up with the master. But, if you are working with an active master, you might want to put a read lock on the database, run the script again, and then make your changes. This is especially true if the changes involve something dynamic like a primary key or auto-increment column, as those values could change while you are trying to run the statements.

Let’s see what happens when we someone else makes some changes and we aren’t aware of the changes. But first, I will start the slave so it can catch up to the master (for the earlier missing statements). We will then stop the slave and let someone else make a few changes. And then we can run the mysqldbcompare utility again:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 \
  --server2=scripts:scripts999@192.168.1.122 cbgc:cbgc \
  --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.122: ... connected.
# Checking databases cbgc on server1 and cbgc on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Activity_Affiliate                      pass    pass    pass    
# TABLE     FedEx_2nd_Day                           pass    pass    pass    
# TABLE     FedEx_Express_Saver                     pass    pass    pass    
# TABLE     FedEx_Ground                            pass    pass    pass    
# TABLE     FedEx_Home                              pass    pass    pass    
# TABLE     FedEx_Priority_Overnight                pass    pass    pass    
# TABLE     FedEx_Standard_Overnight                pass    pass    pass    
# TABLE     Orders                                  pass    pass    pass    
# TABLE     USPS                                    pass    pass    pass    
# TABLE     activity                                FAIL    pass    pass    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE cbgc.activity 
  DROP PRIMARY KEY, 
  DROP COLUMN Dummy_Field, 
  ADD PRIMARY KEY(serial_id);


# TABLE     comics                                  pass    pass    pass    
# TABLE     coupons                                 FAIL    pass    pass    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE cbgc.coupons 
  CHANGE COLUMN Coupon_Notes Coupon_Notes varchar(100) NULL;


# TABLE     customer                                pass    pass    pass    
# TABLE     giftcert                                pass    pass    pass   

Databases are consistent.
#
# ...done

This time, the script took about 20 seconds to run:

real	0m20.058s
user	0m0.452s
sys	0m0.224s

We can see from the output that a column named Dummy_Field was dropped. We can also see that the Coupon_Notes column has changed (or is different on the master). I can now take these changes and implement them on the slave (again assuming that these changes aren’t waiting to be sent to the slave). In my case, once I start the slave, the changes will propagate over to the slave, but there may be cases where that transaction was lost or skipped on the slave (for example, if you had to do a SET GLOBAL sql_slave_skip_counter = N on the slave). You now have the ability to easily make the slave the same as the master by executing these differences. If the changes are too complicated, then you might want to look at re-creating your slave.

There are many different options and output possibilities, so I would encourage you to just try mysqldbcompare and see how it works for you. And as always, feel free to post your feedback in the comments section.

 


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.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

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.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

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

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 the slave. (see my earlier post – 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.

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.

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).

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.

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.

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.

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:

/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:

--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.

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.

Now you can shutdown your MySQL instance.

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.

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:

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 and mysql-bin.index files in the mysql data directory to the mysql Unix user.

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.

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.

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.

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 the mysql_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 Accounts for specific information for your operating system.

An easy way to change the root password is to use mysqladmin from a command prompt:

$ ./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@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:

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.

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.

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:

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.

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 -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.

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 variable gtid_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 was 82F20158-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.

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.

$ 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:

root@macserver01: $ grep CREATE *sql | wc -l
      81

The total number of transactions executed so far is 162551. If we subtract the number of INSERT 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.

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:

  • 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 and mysql-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;”
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“;

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.

Before we turn on the slave, you may check the status of the 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:

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

After a few moments, you can check on the slave status again.

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-3 shows 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.

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”.

To skip this error and continue with replication, you can set the SQL_SLAVE_SKIP_COUNTER to “1”, which tells the slave to skip one transaction. You must stop the slave, set SQL_SLAVE_SKIP_COUNTER = 1 and restart the 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)

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.

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:

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:

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.

We can also check to make sure that all of the users that we created on the master are also now on the slave:

SLAVE:

mysql> select user, host from user order by user, host;
+-------------+-----------------------+
| user        | host                  |
+-------------+-----------------------+
| WebSite     | 192.168.1.2           |
| WebSite     | localhost             |
| replicate   | 192.168.1.121         |
| replicate   | 192.168.1.2           |
| replicate   | 192.168.1.4           |
| replicate   | localhost             |
| root        | 127.0.0.1             |
| root        | 192.168.1.2           |
| root        | VM-Mac-1081-128.local |
| root        | localhost             |
| scripting   | 192.168.1.121         |
| scripting   | 192.168.1.122         |
| scripting   | 192.168.1.2           |
+-----------+-------------------------+
13 rows in set (0.01 sec)

MASTER:

mysql> select user, host from user order by user, host;
+-------------+---------------+
| user        | host          |
+-------------+---------------+
| WebSite     | 192.168.1.2   |
| WebSite     | localhost     |
| replicate   | 192.168.1.121 |
| replicate   | 192.168.1.2   |
| replicate   | 192.168.1.4   |
| replicate   | localhost     |
| root        | 127.0.0.1     |
| root        | 192.168.1.2   |
| root        | localhost     |
| root        | macserver01   |
| scripting   | 192.168.1.121 |
| scripting   | 192.168.1.122 |
| scripting   | 192.168.1.2   |
+-----------+-----------------+
13 rows in set (0.00 sec)

The only difference in the users on the master and slave is the root user for each machine:

Slave - root, VM-Mac-1081-128.local
Master - root, macserver01

The master and slave are now ready for use. If you have an Enterprise subscription to MySQL, you can use MySQL Enterprise Monitor to look at the status of the master and the slave:

Otherwise, you may just do a periodic “show master status\G” and “show slave status\G” to see if the slave is lagging behind the master.

 


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.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

Retrieving List of MySQL Users and Grants with Perl

Before I upgrade MySQL to the latest and greatest version, one of the first things that I do is export the user and grant information. In the past, I would keep all of my user information (user name, password, grants) in a text file, with the SQL for each user/grant ready to be executed on the upgraded server. I did use my own form of “mental encryption” for my passwords, so the passwords weren’t in plain English. But then I would have to decode my passwords each time before I executed the SQL statements.

When I upgrade, I usually like to dump all of the data and import it into the new version, so I have a fresh copy of the database. The MySQL server that I have is for my personal use and the data size is relatively small, so for my case it doesn’t take long to import the data.

But there were times when I would add a user in the MySQL database and forget to add it to my text file. Then, when it came time to upgrade and I would refer to my text file, these new users would not get recreated. For me it wasn’t that big of a deal, as I am only dealing with my own home-office server, and not a production server. I would eventually figure out that these users weren’t available, and I would simply recreate them. But I often add temporary users for testing purposes. Every once in a while I would want to keep some of the temporary users, and some users would be deleted after the testing was completed. So my text file was rarely up to date.

I am in the process of upgrading my servers to MySQL 5.6.9, and I decided to write a quick Perl script to export all of the users, passwords and grants. Since I will be doing a fresh install of MySQL, I can then just use the output from this script to recreate my users. Of course, you need to run this script before the upgrade.

There are two SQL statements that we will execute with this script. The first, is simply retrieving a list of the user and host names from the mysql.user table:

mysql> SELECT user, host FROM user order by user, host;
+-------------+---------------+
| user        | host          |
+-------------+---------------+
| replicate   | 192.168.1.121 |
| replicate   | 192.168.1.2   |
| replicate   | 192.168.1.4   |
| replicate   | localhost     |
| root        | 127.0.0.1     |
| root        | 192.168.1.2   |
| root        | localhost     |
| root        | macserver01   |
| scripting   | 192.168.1.121 |
| scripting   | 192.168.1.122 |
| scripting   | 192.168.1.2   |
+-----------+---------------+
11 rows in set (0.00 sec)

Then, we will loop through each of the user and host names to retrieve their grants and passwords. Here is a sample of retrieving this data for just one user and one host name:

mysql> SHOW GRANTS FOR 'replicate'@'192.168.1.121';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for replicate@192.168.1.121                                                                                |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.121' IDENTIFIED BY PASSWORD '*BF6F715A6EBFE63005BEB705C' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The script will create the necessary SQL statements to recreate the users and grants, with the output displayed in the terminal window. You will just need to copy and save the output from the terminal window into a text file. Or you can change the print statement to output the information directly to a file. Here are the changes to output to a file:

Add this to the top of the script after the “my $Database” line.

open(OUTFILE, ">mysql_usernames.txt") || die "Can't redirect stdout";

Add OUTFILE after each of your print commands:

print OUTFILE "CREATE user \'$user\'\@'$host\'\ identified by '';\n";
print OUTFILE "$privileges;\n\n";

And close the OUTFILE after the last right curly bracket “}” and before the subroutine:

close OUTFILE;

For the script, you will need to install the Perl::DBI module. You will also need to change the values for the $host, $userid and $passwd variables in the sub routine ConnectToMySql to match your system. Here is the Perl script.

#!/usr/bin/perl

use DBI;

my $Database = "mysql";

        $dbh = ConnectToMySql($Database);

        # retrieve a list of users and host names
	$query = "SELECT user, host FROM user order by user, host";
	
        $sth = $dbh->prepare($query);

        $sth->execute();
    
          while (@data = $sth->fetchrow_array()) {
            my $user = $data[0];
            my $host = $data[1];
            
            print "CREATE user \'$user\'\@'$host\'\ identified by '';\n";

                $dbh2 = ConnectToMySql($Database);

	        # retrieve the grants for each user and host combination
                $query2 = "SHOW GRANTS FOR '$user'\@'$host'";
	
                $sth2 = $dbh2->prepare($query2);

                $sth2->execute();

                        while (@data2 = $sth2->fetchrow_array()) {
                            my $privileges = $data2[0];
                            print "$privileges;\n\n";
                        }

          # end first while statement           
          }

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

   my ($db) = @_;

   my $host ="";
   my $userid = "";
   my $passwd = "";
   my $connectionInfo = "dbi:mysql:$db;$host";

   # make connection to database
   my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
   return $l_dbh;

}

And here is the output from running the script.

root@macserver01: $ perl get_mysql_users.pl
CREATE user 'replicate'@'192.168.1.121' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.121' IDENTIFIED BY PASSWORD '*BF6F715A6EBF367E76X705C';

CREATE user 'replicate'@'192.168.1.2' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.2' IDENTIFIED BY PASSWORD '*BF6F715A6EBF367E76X705C';

CREATE user 'replicate'@'192.168.1.4' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.4' IDENTIFIED BY PASSWORD '*2A9C19E10B309BF1BE40E4A9C';

CREATE user 'replicate'@'localhost' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'localhost' IDENTIFIED BY PASSWORD '*2A9C19E10B309BF1BE40E4A9C';

CREATE user 'root'@'127.0.0.1' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION;

CREATE user 'root'@'192.168.1.2' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION;

CREATE user 'root'@'localhost' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION;

CREATE user 'root'@'macserver01' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'macserver01' WITH GRANT OPTION;

CREATE user 'scripting'@'192.168.1.121' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.121' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION;

CREATE user 'scripting'@'192.168.1.122' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.122' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION;

CREATE user 'scripting'@'192.168.1.2' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.2' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION;

Now when I upgrade my server, I can simply run this script prior to my upgrade and save the information. I don’t have to worry about missing a user or keeping my user information in a text file.

 


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.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

MySQL Replication – Multi-Threaded Slaves (Parallel Event Execution)

If you aren’t familiar with MySQL replication, “Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default – slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.” (From: http://dev.mysql.com/doc/refman/5.5/en/replication.html).

I use MySQL replication on my home office server. I don’t really have much data to store, but it is nice to have several replicated slaves for backup purposes and also for testing new replication features of MySQL. I also use my setup to demo MySQL Enterprise Monitor.

Prior to MySQL 5.6.3, replication slaves were single-threaded. There are three threads involved with replication, but only one of those threads writes the replicated data from the master to the slave database (more info about these threads may be found here). If you had a busy master server, with a high number of writes, these writes could get bottlenecked at the slave, as the slave could only apply each event one at a time, in the same order that the events were executed on the master. If you were using the slaves for your reads, then your data could be stale depending on how quickly (or slowly) the slaves could apply the writes from the master.

I was giving a presentation a couple years ago about MySQL replication. I was trying to demonstrate that adding slaves doesn’t necessarily divide the workload equally among the number of servers that you have. In my example, I stated that we had a master server that had a max load (at 100%) of 10,000 events (reads and writes) per a given time frame. For these 10,000 events, we assumed that the master was performing 6,000 reads and 4,000 writes during this period. If we added three slaves, we couldn’t figure that the master would now be at 25% capacity (100% capacity divided by the new total of four servers). Each slave also has to perform the same number of writes as the master – we were really only scaling the number of reads across four servers.

In order to try and get my point across, I then presented a formula that looked like this:

Master server alone:
Max. Load = 6000 reads + 4000 writes / 10,000

Master Server plus three slaves:
Load = (6000 reads + (4 x 4000 writes) / (4 servers x 10,000)) = (22,000 / 40,000) = 55%

Someone in the audience then pointed out that this formula wouldn’t work exactly, as the slaves were single-threaded. Of course that was a true statement, but the point that I was trying to make is that scaling out with slaves isn’t exactly linear. Later, I tried to come up with an easy way to determine a formula that would take into account the fact that slaves were single-threaded, but I couldn’t find an easy way to do this. There were just too many factors involved. For future presentations, I just made sure that I added the caveat that slaves are single-threaded.

So, what are multi-threaded slaves? Multi-threaded slaves allow you to execute the replication events from a master across different databases in parallel. For best results, you should partition your data per database. In other words, instead of having one database with many tables, you would have a database for each table. If you have some tables that are read-only and you rarely write to these tables, you could include them in their own database or in another database. By splitting the data into multiple databases, MySQL replication is able to update each database separately, in the same order relative to the updates as they occurred on the master. There is a system variable named slave_parallel_workers, which should be set to equal the same number of databases that you have. There are some concerns in using multi-threaded replication, as events from the master might not be executed on the slave in the same order. Click here for more information on MySQL multi-threaded replication (Parallel Event Execution).

Back in April, 2012, Mat Keep wrote about Benchmarking MySQL Replication with Multi-Threaded Slaves in which he demonstrated that multi-threaded slaves could improve slave performance by almost five times. Mat’s example involved inserting 10,000 rows into 10 different schemas on a single slave. Mat stated that there are three key variables that you need to set to achieve maximum performance with multi-threaded slaves:

binlog-format=STATEMENT 
relay-log-info-repository=TABLE 
master-info-repository=TABLE 

(From http://dev.mysql.com/doc/refman/5.6/en/slave-logs-status.html: use ––master-info-repository to have the master info log written to the mysql.slave_master_info table, and use ––relay-log-info-repository to have the relay log info log written to the mysql.slave_relay_log_info table)

Mat also suggests that the variable slave_parallel_workers should be set to equal the number of schemas that you have. If you look at the data he provided, increasing this variable beyond the total number of schemas did not improve performance. Even though the slave is multi-threaded, each schema is still single-threaded, so having multiple worker threads didn’t really make a difference.

While I don’t have the need at this point to switch my replication setup to use multi-threaded slaves, it is still a great new feature of 5.6. You may download a copy of MySQL at http://dev.mysql.com/downloads/mysql. As of this writing, 5.6 is under the Development Releases tab.

 


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.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

MySQL 5.6 Delayed Replication – Making a Slave Deliberately Lag Behind a Master

In the majority of MySQL replication scenarios, you want your slave databases to be a mirror of your master databases. You usually don’t want your slave to be behind your master by more than a few seconds – and your main goal is for your slave to always be in sync with your master. Would you ever want your slave to deliberately be a few seconds, minutes or even hours behind your master? There have been several suggestions from MySQL users over the years regarding this functionality as “feature request” (even though most of the requests were submitted as MySQL “bugs”, which was the easiest way to submit such a request).

The first request (that I could find) was by Jason Garrett, back in August of 2006, and was logged as “bug 21639”. Jason wanted MySQL to “provide a parameter/setting which allows an administrator to specify how many seconds a replication slave will be behind a replication master. This will have the effect of delaying the replcation slave for this number of seconds. This is useful in circumstances where the replication master is at risk of major data change/loss, and allow an administrator to intervene and isolate the slave from the impact. ie. In an environment where an adminstrator may accidentally drop a table.”

Anders Henke followed up the next month (September 2006) with this request (bug 22072) – “Sometimes it’s also very nice being able to look on how some specific record did look last week – before you’ve made those critical changes yesterday, which might (or might not) relate to some issue being reported to you. And of course also without reloading that large database from backups.”

There have been a couple more requests related to delayed replication, and while there were some work-arounds, it is now a feature of 5.6.

On the MySQL web site (in blue below), it gives three examples of why you would want to use time-delayed replication (including what Kay and Anders wanted to do).

Scenario #1 – To protect against user mistakes on the master. A DBA can roll back a delayed slave to the time just before the disaster.

If you have worked with databases for any period of time, I am sure that you have had the experience of accidentally deleting some rows or truncating a table. And, if your database hasn’t had a recent backup, then that data could be lost. And, if you have a fast slave, then that errant command will be executed before you can figure out what you just did. So, you can then forget about using your slave as a backup for that lost data. By setting your slave to lag behind your master for 10 minutes (more or less), you should have enough time to go to the slave, stop it, and export the data that you just lost for import into the master database. Of course you can set this delay for a longer or shorter period, but it should be long enough for you to do what you need to do in order to at least stop the replication event from executing on the slave.

Scenario #2 – To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the slave. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging slave.

You can test how your particular application and system behaves when the slave is lagging behind the master. Normally you would generate a huge load on the master so that the slave is bogged down and thus a lag is generated. By delaying the slave, you can simulate this lag without having to generate the load on the master. You can also use this to debug any applications or conditions that are related to a lagging slave.

Scenario #3 – To inspect what the database looked like long ago, without having to reload a backup. For example, if the delay is one week and the DBA needs to see what the database looked like before the last few days’ worth of development, the delayed slave can be inspected.

Having a slave delayed by a long time – for example, a few days or longer – can give you a snapshot of what the database looked like before the last round of database development. If you are working on an application and are making changes that effect the database, you can compare the two database states. You might wonder “how would the application perform if I made these changes to the database?”. You could then make the changes and test the application on both servers to see the differences.

To set the amount of time that your slave will log behind the master, simply execute this command on the slave (you have to stop the slave first):

CHANGE MASTER TO MASTER_DELAY = N;

– where “N” is the number of seconds that you want the slave to lag behind the master.

On a slave, let’s execute this statement so that the slave is 10 minutes (600 seconds) behind the master. But, for comparison, let’s do a “show slave status\G” on the slave before we make the change:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.121
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 798706
               Relay_Log_File: WEB_SERVER_01-relay-bin.000052
                Relay_Log_Pos: 63004
        Relay_Master_Log_File: mysql-bin.000019
             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: 798706
              Relay_Log_Space: 63491
              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: 4
                  Master_UUID: bf0fc6b6-3b3a-11e2-99fd-32f021d3be40
             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: 6CD03F68-3B38-11E2-99FA-588CB3DE3E9D:1-6:8-24:26-1497,
BF0FC6B6-3B3A-11E2-99FD-32F021D3BE40:1-1036
            Executed_Gtid_Set: 6CD03F68-3B38-11E2-99FA-588CB3DE3E9D:1-6:8-11:13-16:18:20-24:26-1497,
BF0FC6B6-3B3A-11E2-99FD-32F021D3BE40:1-1036,
E6DF8108-4BB2-11E2-AB25-ECEFE5B0C1B4:1-378
1 row in set (0.00 sec)

Now, let’s execute the statement: (we have to stop the slave first, execute the statement, and restart the slave)

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.07 sec)

mysql> CHANGE MASTER TO MASTER_DELAY = 600;
Query OK, 0 rows affected (0.06 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

Here is the “show slave status\G” command after the statement was executed:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.121
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 798706
               Relay_Log_File: WEB_SERVER_01-relay-bin.000002
                Relay_Log_Pos: 314
        Relay_Master_Log_File: mysql-bin.000019
             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: 798706
              Relay_Log_Space: 526
              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: 4
                  Master_UUID: bf0fc6b6-3b3a-11e2-99fd-32f021d3be40
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 600
          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: 
            Executed_Gtid_Set: 6CD03F68-3B38-11E2-99FA-588CB3DE3E9D:1-6:8-11:13-16:18:20-24:26-1497,
BF0FC6B6-3B3A-11E2-99FD-32F021D3BE40:1-1036,
E6DF8108-4BB2-11E2-AB25-ECEFE5B0C1B4:1-378
1 row in set (0.01 sec)

SHOW SLAVE STATUS has three fields that provide information about the delay, and you can see their values in the above output: (from http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html in blue)

1. SQL_Delay: A nonnegative integer indicating the number of seconds that the slave must lag the master.

                    SQL_Delay: 600

You can see above that it has changed to 600.

2. SQL_Remaining_Delay: When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event, this field contains an integer indicating the number of seconds left of the delay. At other times, this field is NULL.

The value of “SQL_Remaining_Delay” from my “show slave status\G” command shows “NULL“.

          SQL_Remaining_Delay: NULL

The value is “NULL” because nothing has changed on the master that hasn’t been updated on the slave, so it doesn’t show the remaining seconds that the slave will lag behind the master. I changed a record on the master, executed the “show slave status\G” command again, and you can see the SQL_Remaining_Delay status changed:

                    SQL_Delay: 600
          SQL_Remaining_Delay: 592
      Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

Also, if you are running the Enterprise version of MySQL and are using MySQL Enterprise Monitor (MEM), you will now see a change in the “Time Behind” column under the replication tab for this particular server, which is labeled as VM-Mac-1081-123.

Here is a snapshot from MEM before I updated the master:

And here is a screenshot from MEM after I updated the master, which shows the slave starting to lag behind the master, as it is waiting 600 seconds before applying the update:

Once the slave has executed all statements on the master, in MEM you will see that the “Time Behind” column for VM-Mac-1081-123 will reset back to 00:00:00. This does not mean that the slave isn’t still lagging behind the master by 600 seconds. It just shows that the slave is current with the master.

In my MEM example, I am running a slave (VM-Mac-1081-123) off another slave (with IP address of 192.168.1.121), which explains why MEM is stating that 192.168.1.121 is not being monitored. That MySQL instance is being monitored, but it is a slave to another master (192.168.1.2), and is being monitored below the screenshot that I provided and is not shown in this example.

3. Slave_SQL_Running_State: A string indicating the state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value of the SQL thread as displayed by SHOW PROCESSLIST.

You can see that the Slave_SQL_Running_State value from my “show slave status\G” command is as follows:

      Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

The above value shows that we have pending statements on the master that have not been applied to the slave.

Let’s run the SHOW PROCESSLIST statement, but at this time we do not have any pending statements on the master: (I removed all other processes that aren’t related to this discussion)

mysql> show processlist;
+-----+-------------+-----------------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id  | User        | Host            | db    | Command | Time | State                                                                       | Info             |
+-----+-------------+-----------------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
| 130 | system user |                 | NULL  | Connect |  379 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 129 | system user |                 | NULL  | Connect |  379 | Waiting for master to send event                                            | NULL             |
+-----+-------------+-----------------+-------+---------+------+-----------------------------------------------------------------------------+------------------+

So, even with the slave delay, since we do not have any pending statements from the master, the value of “Slave_SQL_Running_State” looks like the normal value that you have when you are not running a delay and the slave is current with the master. Now I will change something on the master, and re-run the SHOW PROCESSLIST statement:

mysql> show processlist;
+-----+-------------+-----------------+-------+---------+------+----------------------------------------------------------------+------------------+
| Id  | User        | Host            | db    | Command | Time | State                                                          | Info             |
+-----+-------------+-----------------+-------+---------+------+----------------------------------------------------------------+------------------+
| 130 | system user |                 | NULL  | Connect | 1784 | Waiting until MASTER_DELAY seconds after master executed event | NULL             |
| 129 | system user |                 | NULL  | Connect | 2503 | Waiting for master to send event                               | NULL             |
+-----+-------------+-----------------+-------+---------+------+----------------------------------------------------------------+------------------+

You can see that the value of “State” has changed from:

Slave has read all relay log; waiting for the slave I/O thread to update it

to:

Waiting until MASTER_DELAY seconds after master executed event“.

After our MASTER_DELAY value of 600 seconds has passed, the value of “State” will revert back.

And when I run my “show slave status\G” statement again, the value for “Slave_SQL_Running_State” has changed, since I have a pending statement on the master:

      Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

I now have a slave that is lagging 10 minutes behind the master. If I ever have a situation where I accidentally delete some data, I now have 10 minutes to stop the slave and try to retrieve my data from the slave. In a situation like this, I could try to stop the slave from executing the delete statement, but it might be easier to just export the data I need and re-import it back into the master. My deletion command will eventually be executed on the slave, but so will my importing of the deleted data.

 


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.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.