MySQL Replication – Creating a New Master/Slave Topology with or without Virtual Machines

In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.

I am going to create some virtual machines instead of using individual servers. But, you can also use these instructions to create a MySQL replication (master/slave) setup with real servers.

Here is how replication works. On the master server, when there are updates (inserts, updates, deletes, alter, etc.) to the database, MySQL will write the appropriate information to the binlog (binary log), depending upon which replication method you choose.

From: http://dev.mysql.com/doc/refman/5.6/en/binary-log.html

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 16.2, “Replication Implementation“.

Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log“.

The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 5.2.3, “The General Query Log“.

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

From: http://dev.mysql.com/doc/refman/5.6/en/replication-formats.html

Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the master’s binary log. The correlation between binary logging formats and the terms used during replication are:

  • Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard statement-based binary logging format. In older versions of MySQL (5.1.4 and earlier), binary logging and replication used this format exclusively.
  • Row-based binary logging logs changes in individual table rows. When used with MySQL replication, this is known as row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.
  • The server can change the binary logging format in real time according to the type of event using mixed-format logging. When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described later. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 5.2.4.3, “Mixed Binary Logging Format”.

Once a slave has the binlog and binlog position, the slave will connect to the master and retrieve all of the binlog entries from the specified binlog file and after a specified binlog position. The slave creates a thread (IO thread) that connects to the master server. The master server then creates a binlog dump thread, and sends the data to the slave’s IO thread. The slave will in effect retrieve the data that was written to the specified binary log starting after a specific binlog position and then write it to the slave’s relay log (see IO thread states). The slave will then take the data from the relay log and apply it to the slave’s database via the SQL thread. If this isn’t confusing enough, maybe this poorly drawn diagram will help you understand the steps in replication:

For this example, I am starting with a fresh install on one virtual machine running Mac OS X 10.8. MySQL doesn’t have a version specific to 10.8, but I found that the version for 10.7 will work. I am not going to cover how to install MySQL, but I will show you what you need to change in order to make MySQL replication work.

MySQL uses a configuration file (my.cnf or my.ini for Windows) for all of the database variables that will be read by MySQL at startup. After I install MySQL – but before I start MySQL, I will need to choose a copy of the my.cnf file to use, and then modify that file to match my specific use of MySQL. For this example, I am going to just copy the my.cnf file in my MySQL home directory to /etc/my.cnf and then edit the /etc/my.cnf file. You may use the following variables for your my.cnf options file. There are quite a few variables in this file that you will need to change or add. Since the version of the my.cnf file that I copied is mostly blank, here is what I have in my my.cnf file: (And yes, these are not all of the variables that you might need – these are just the basic variables for this example)

[mysqld_safe]
socket = /tmp/mysql.sock

[client]
port=3306

[mysqld]
port = 3306
user = mysql
tmpdir = /tmp
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log_error = /usr/local/mysql/error.log

Since InnoDB is the default storage engine for MySQL (as of 5.5), I want to provide some InnoDB-specific variables:

innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:25M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data

innodb_log_files_in_group = 2
innodb_log_file_size = 25M
innodb_buffer_pool_size = 16M

And then to enable replication, MySQL will need to write all of the select, insert, update and delete etc. statements to the binary log (binlog). I also need to choose the binlog format. I will use “mixed“, which allows MySQL to determine whether or not to use row or statement-based binlog format for each separate SQL statement. The slave will retrieve these statements from the master and then write them to the slave’s relay log before applying these statements to the slave database.

log-bin=mysql-bin
binlog_format=mixed

I need to give each server a unique ID. I always give my master server the id of one (1), and then for the slaves, I will assign a sequential number starting at two for the server-id‘s.

server-id = 1

If you want to possibly use the slave for failover (where you promote the slave to be the master), then you will need to log the updates that are on the slave to the slave’s binary log as well. This will allow any other slaves to use this server as a master. Even though this is a master, if it ever becomes a slave I might want to re-promote it to master at a future date.

log-slave-updates

And in order to enable auto crash recovery on the slaves, enable:

relay-log-recovery

You may now run the installation script for the version of MySQL that you are installing. 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.

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> 

Running the mysqladmin program (above) only changed your root password for the localhost.

mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

Now that you have logged in, you can change your password for all of your root accounts:

mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| 127.0.0.1 | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| ::1       | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

To find and delete the anonymous accounts, you can find a list of all of the 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       |
|      | localhost |
| root | localhost |
+------+-----------+
4 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      |
+------+-----------+
|      | localhost |
+------+-----------+
1 row in set (0.00 sec)

You may now delete the blank users:

mysql> delete from user where user = '';
Query OK, 1 row 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 | localhost |
+------+-----------+
3 rows in set (0.00 sec)

Since we are installing MySQL for the first time on your master, you will need to create 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';

mysql> select user, host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| replicate | %         |
| root      | 127.0.0.1 |
| root      | ::1       |
| root      | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

You will need to make sure that your replication user matches the domain names or IP addresses, so that it has permissions to access the other server(s).

MySQL should now be up and running on your master server. If you aren’t using VM’s, you may now duplicate these same installation steps on your slave server(s) – but you must change the value of server-id in your my.cnf file for each server – to something other than the value that you have for your master server.

If you are working with virtual machines, you will need to:

  • Stop the mysqld process
  • Stop the virtual machine (shutdown the instance)
  • Copy/duplicate the virtual machine
  • Change the IP address of the new virtual machine
  • Change the server-id in the my.cnf file of the new virtual machine
  • Change my server name for use in file sharing (if file sharing is turned on)
  • Generate a new UUID for the new slave servers and edit the auto.cnf file. (more on this below)

Beginning with MySQL 5.6, the MySQL server generates a unique ID (UUID) in addition to the server-id supplied by the user. This is available as the global, read-only variable server_uuid. If you aren’t using VM’s, a new UUID will be installed when you install MySQL. On the new VM copies, we will need to generate a new UUID and then edit the auto.cnf file. You can run the select UUID(); command from the master server:

mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 1da4ab9c-7baf-11e2-930f-6a4c3f56f0b5 |
+--------------------------------------+
1 row in set (0.00 sec)

Next, edit the auto.cnf file that is in the MySQL home data directory:

# cat auto.cnf 
[auto]
server-uuid=1da4ab9c-7baf-11e2-930f-6a4c3f56f0b5

Edit the auto.cnf file with a text editor (or use vi) and change the old UUID to the new UUID (example – change 33e3daac-79e5-11e2-9862-ec1bc27a1e29 to 1da4ab9c-7baf-11e2-930f-6a4c3f56f0b5).

After you have installed MySQL on the new slave or copied the original VM and repeated the steps above, you can check to see if your servers have unique server-id‘s and UUID‘s. Login to each instance of mysql:

# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, 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> use mysql;
Database changed
mysql> show variables where variable_name = 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables where variable_name = 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 33e3daac-79e5-11e2-9862-ec1bc27a1e29 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

Now that we have our servers up and running, we need to tell our slave server(s) about the master server. If we have already started inserting data into our master servers, we need to put a read lock on the master, show the master status to get the binlog and binlog position of the master, and then release the lock. You may do this on one line separated by a semi-colon to reduce the amount of time that the lock is in place:

mysql> FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 |      540 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

It is important that you save the SHOW MASTER STATUS information, as you will need this for each slave.

Now we need to tell the slave where the master is located, which binlog file to use, and which position to start. Issue this CHANGE MASTER TO command on the slave server(s): (don’t forget to change the values to match your master server)

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='master IP address',
    ->   MASTER_USER='replication user',
    ->   MASTER_PASSWORD='replication user password',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000015',
    ->   MASTER_LOG_POS=540,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.27 sec)

We have two warnings from the above statement. Let’s look at the warnings:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master.info repository 
is not secure and is therefore not recommended. Please see the MySQL Manual for more 
about this issue and possible alternatives.
2 rows in set (0.00 sec)

The first error “Sending passwords in plain text…” can be ignored. Since we are setting up replication using this method, we have to send the user name and password as plain text. The second error explains that the information in the CHANGE MASTER TO statement is stored in a non-secure file named master.info in your MySQL data directory:

# pwd
/usr/local/mysql/data
# ls -l master.info
-rw-rw----  1 _mysql  wheel  99 Feb 20 15:26 master.info
# cat master.info
23
mysql-bin.000015
540
192.168.1.2
replicate
replicate999
3306
10
....

There are options to not using the master.info file: “MySQL 5.6 extends the replication START SLAVE command to enable DBAs to specify master user and password as part of the replication slave options and to authenticate the account used to connect to the master through an external authentication plugin (user defined or those provided under MySQL Enterprise Edition). With these options the user and password no longer need to be exposed in plain text in the master.info file.” (from https://blogs.oracle.com/MySQL/entry/mysql_5_6_is_a)

For this example, we don’t need to worry about these errors. To begin replication, we need to start the slave:

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

We now can check the status of the slave to see if it is working as a slave, with the SHOW SLAVE STATUS\G command:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.181
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 540
               Relay_Log_File: macos-108-repl02-relay-bin.000002
                Relay_Log_Pos: 1551
        Relay_Master_Log_File: mysql-bin.000015
             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: 540
              Relay_Log_Space: 1735
              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: 33e3daac-79e5-11e2-9862-ec1bc27a1e29
             Master_Info_File: /usr/local/mysql-advanced-5.6.10-osx10.7-x86_64/data/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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

Two values to note in the slave status shows us that our CHANGE MASTER TO statement worked:

              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 540

We can now execute a statement on the master, to see if it propagates to the slave database. Let’s see what databases are on the master:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

When we execute the same command on the slave, we get the same results. Since we already have a test database, let’s create a table in that database. We can check to see if there are any tables in that database already:

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

Currently there aren’t any tables in the test database. We can now create one on the master database:

mysql> CREATE TABLE `address` (
    ->   `serial_number` int(6) NOT NULL AUTO_INCREMENT,
    ->   `last_name` char(40) NOT NULL DEFAULT '',
    ->   `first_name` char(40) NOT NULL DEFAULT '',
    ->   `address_01` char(40) NOT NULL DEFAULT '',
    ->   `city` char(30) NOT NULL DEFAULT '',
    ->   `state` char(20) NOT NULL DEFAULT '',
    ->   `zip` char(11) NOT NULL DEFAULT '',
    ->   `phone` char(15) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`serial_number`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| address        |
+----------------+
1 row in set (0.00 sec)

And let’s take a look at the new master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 |     1808 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

So, if we have replication set up correctly, when we go to the slave, we should see this table on the slave as well. Let’s execute the same same show table statement on the slave:

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| address        |
+----------------+
1 row in set (0.00 sec)

Replication is now up and running. You may continue these steps for additional slaves that you install manually or slaves where you copy the VM.

I can also use the MySQL Utility script mysqldbcompare to see if both tables are the same. I wrote about mysqldbcompare in an earlier post. Just like this post, on the master and slave databases, I will create a user named “scripts” to execute the mysqldbcompare script: I don’t need to actually create the user on the slave, as when I execute this command on the master, it will be replicated over to the slave.

mysql> CREATE USER 'scripts'@'%' IDENTIFIED BY 'scripts999';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'scripts'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

I can now run the mysqldbcompare script:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.181 --server2=scripts:scripts999@192.168.1.182 test:test --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.181: ... connected.
# server2 on 192.168.1.182: ... connected.
# Checking databases test on server1 and test on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     address                                 pass    pass    pass   

Databases are consistent.
#
# ...done

The SQL statement that I executed on the master to create the table “address” has been replicated on the slave, so replication is running and confirmed to be working.

 


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.

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.

Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication

This post is the second 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.

I am going to show you one way that you can use the mysqlfailover script to monitor your replication stack and automatically failover to a slave database when your master has failed. You will need to have both your master and slave databases running with GTID’s enabled. I will provide a brief overview of GTID’s, and how to start replication with GTID enabled. The term “automatically failover” in the title might be a bit misleading, as the failover process is automatic, but it does take a couple of minutes. It is automatic but not instantaneous. Also, you may use the mysqlfailover script on a master with multiple slaves, but in this example I will only have one master and one slave.

Let’s start with a quick review of GTID’s – or global transaction identifiers. GTID’s were introduced in MySQL 5.6.5. With GTID’s, each transaction can be identified and tracked as it is committed on the originating server and applied by any slaves; this means that it is not necessary when using GTIDs to refer to log files or positions within those files when starting a new slave or failing over to a new master, which greatly simplifies these tasks.

(From http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html)

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction when it is committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.

The GTID has this format: GTID = source_id:transaction_id – with the source_id identifying the originating server (in this case, the master server), and the transaction_id being a sequential number of the transactions that were committed on the originating server. For example, the twenty-third (23rd) transaction to be committed originally on the server having the UUID 3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

(From http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html.)

When you provide your slave server(s) with the information about which master to use for replication, without using GTID’s, you would normally execute a statement like this on the slave:

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.121',
  MASTER_USER = 'replicate',
  MASTER_PASSWORD = 'password',
  MASTER_PORT = 3306,
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 150691098,
  MASTER_CONNECT_RETRY = 10;

When you have GTID’s enabled, you don’t have to provide the log file and position, you only have to provide this:

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.121',
MASTER_PORT = 3306,
MASTER_USER = 'replicate',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION = 1;

If you have worked with replication before, this should make some sense. If not, then you will probably want to read more about replication and GTID’s.

For this example, I am going to take an existing MySQL database, export the data, install the latest version of MySQL (in this case 5.6.8), enable GTID’s, and then demo the mysqlfailover script. This post is going to be a long one, but I will not try to go into as much detail as I normally would. I am writing this after I have already tested this – so I am writing from memory – and hopefully I won’t forget any steps. Here is my current configuration:

I have an application and web server at 192.168.1.2, a MySQL master server at 192.168.1.121 and a MySQL slave server at 192.168.1.122.

For my master and slave servers, I was running MySQL version 5.5.27. To export the data, I am going to just use mysqldump – but I will not export any of the MySQL tables (such as information_schema, mysql, performance_schema and test). When I upgrade from a new major version of MySQL (such as from 5.5. to 5.6), I like to start with a new install versus trying to upgrade from a previous version. (For large databases, this might not be as efficient or even possible, but since my database dump is only 26 megabytes, this will work for me.)

Since I only have a few MySQL users, I keep the SQL statements that I need to re-create these users and their permissions in a text file. Obviously this isn’t the best and most secure way to do this, but this is for my home system, so it doesn’t matter in my case. If you prefer, you can just upgrade from 5.5 to 5.6 and not export the data – and instructions for upgrading this way may be found via this link – Installing and Upgrading MySQL.

You need to make sure that there aren’t any updates to the database while you are doing your mysqldump. You can lock the database with this command from a mysql prompt FLUSH TABLES WITH READ LOCK; and then unlock it with UNLOCK TABLES;. Here is the mysqldump command that I used:

/usr/local/mysql/bin/mysqldump --databases [list of your databases here, separated by spaces] 
--add-drop-database --add-drop-table --user=root --pass= > /users/tonydarnell/2012_11_30_1645_dbdump.db

(Yes, you will get a notification “Warning: Using a password on the command line interface can be insecure.” so you could leave the password blank and enter it when prompted.)

I usually also create a backup of the entire database in case I have any problems or if I destroy something.

/usr/local/mysql/bin/mysqldump --all-databases --add-drop-database --add-drop-table --user=root \
  --pass=[your_password]
 > /users/tonydarnell/2012_11_30_1645_all_dbdump.db

I keep my data directory on two external USB hard drives that I have set up as a RAID on each machine. I use a symbolic link from my MySQL data directory (/usr/local/mysql/data) to point to a directory on the RAID – with this command ln -s /volumes/server_raid/mysql_data/data /usr/local/mysql/data). Since I am creating a new install, I just rename the directory on the raid mv /volumes/server_raid/mysql_data/data /volumes/server_raid/mysql_data/data-old. When I install MySQ it will create a new data directory. I then can move the new data directory to the RAID, and recreate the link. You could also use this method to move your data directory to another internal or SSD drive.

Now I install MySQL version 5.6.8. (see http://dev.mysql.com/doc/refman/5.6/en/installing.html for instructions on installing MySQL.)

Once I have MySQL 5.6.8 installed on the master (including running any post-install scripts per the instructions above), I can import my database.

mysql -uroot -p < /users/tonydarnell/2012_11_30_1645_all_dbdump.db

I then start MySQL, login, create my users, and the master is finished and ready. I then repeat the same procedures on a slave machine. Since no one has updated the master since my data dump, the master and the slave should be exact copies of each other. If you are using virtual machines, once you have created the first virtual machine to be used as your master, you can just duplicate the VM to be your slave machine. Just be sure to change the server-id option in your mysql config file (my.cnf or my.ini) to be a different number.

It is time to turn on GTID's and to get replication started. If you already have a master and slave configured, then you can refer to this link on how to start replication using GTID's http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html.

You can either start the GTID process on both MySQL servers by adding these options when you start mysqld:

--gtid_mode=ON --log-bin --log-slave-updates --disable-gtid-unsafe-statements

Or, you can add these options to your MySQL config file (/etc/my.cnf or c:\my.cnf or c:\Windows\my.ini).

gtid_mode=ON
disable-gtid-unsafe-statements = 1
log-bin
log-slave-updates

Binary logging should be enabled on the master, and you will also want to enable binary logging on the slave, so when the slave is promoted to the master, you can make the old master a slave to the new master. See this link for binary log options and variables.

(Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency)

Now that you have both of these options in place, you may start both of your servers. On the slave, you will want to add –skip-slave-start to the mysqld command. You will want to start the slave manually, after you have given the slave the information about the master from a mysql prompt:

To test and make sure that GTID is running, you may issue this command on both servers:

mysql> show global variables like '%GTID%';
+--------------------------------+--------------------------------------------+
| Variable_name                  | Value                                      |
+--------------------------------+--------------------------------------------+
| disable_gtid_unsafe_statements | ON                                         |
| gtid_done                      | 6CD03F68-3B38-11E2-99FA-588CB3DE3E9D:1-242 |
| gtid_lost                      |                                            |
| gtid_mode                      | ON                                         |
| gtid_owned                     |                                            |
+--------------------------------+--------------------------------------------+
5 rows in set (0.00 sec)

You can see the value for gtid_done contains the GTID information – source_id:transaction_id, where 1-242 is the range of transactions that have been committed. (Your values will be different)

Now that we have MySQL replication running with GTID’s enabled, we can look at running the mysqlfailover script. I use Perl on my web site, and my Perl scripts make a connection to the MySQL database by reading the connection information from a text file (connection file) stored on the web server in the CGI directory. (for more information on how I use this connection file, please see Connecting to MySQL with Perl)

This connection file contains the database name, IP address, mysql user and password. This file determines which MySQL server will be used by the web server and in this example the file is named accessWEB. The file contains the following: (you will have to configure the file to match your system)

scripts_db
192.168.1.121
user_name
password

With the mysqlfailover script, you have the option to run a script before failover and after failover. There is an option to also run a script prior to failing over, and one to run a script after failover has finished and mysqlfailover has refreshed the health report.

For this test, I will create a script that will change the connection file information to point to the slave database when the master fails. This is as simple as creating a new connection file with the slave’s information, and then copying it on top of the existing file. I will create a file for each server, and name the files after their IP addresses. So, the file 192-168-1-121.txt will have the same information as the current accessWEB connection file, and the file 192-168-1-122.txt will contain:

scripts_db
192.168.1.122
user_name
password

For my pre-failover script, I will then create a shell script that input some text into a file so that I can see when failover started. The script will be named “prefail.sh”, and it will contain the following:

cd /Library/WebServer/cgi-bin/
echo "failover started" > failover_started.txt

I will need to make sure that prefail.sh has execute privileges and that all of the connection files have the correct privileges as well. And I would want to test the script prior to using it.

For my post-failover script, I will create a shell script that will send me a text message, will change the connection file after failover has occurred and input some text into a file so that I can see when failover finished. I will name this script postfail.sh. It will contain the following:

cd /Library/WebServer/cgi-bin/
cp 192-168-1-122.txt accessWEB
echo "Failover has occurred." | mail 4045552232@messaging.att.net
echo "failover finished" > failover_finished.txt

I have my master and slave using GTID, and the web server is connecting to the master (192.168.1.121). I can now run the mysqlfailover script. I don’t want to run it on the master or slave, because if one of them fails, then the script could fail as well. I will run the script on the web server. If it fails, then it doesn’t matter if the MySQL servers are down, as no one can access the web site anyway.

Prior to running this script, I created a MySQL user name “scripts” to use for the mysqlfailover script. I gave the user the same permissions as root. I have a few options that I will use when executing the mysqlfailover script:

--master=scripts:scripts123@192.168.1.121:3306 - connection information for the master
--slaves=scripts:scripts123@192.168.1.122:3306 - connection information for the slave(s)
--candidates=scripts:scripts123@192.168.1.122:3306 - a list of candidates for failover
--exec-before=/users/tonydarnell/scripts/prefail.sh - the script to execute before the failover
--exec-after=/users/tonydarnell/scripts/postfail.sh - the script to execute after the failover

There is an option for setting the refresh time for the script with the –interval=X option (where X is the number of seconds for the interval), but I will be using the default of 15 seconds.

I am also adding the –force option – because at startup, the console will attempt to register itself with the master. If another console is already registered, and the failover mode is auto or elect, the console will be blocked from running failover. When a console quits, it deregisters itself from the master. If this process is broken, the user may override the registration check by using the –force option.

(From: http://dev.mysql.com/doc/workbench/en/mysqlfailover.html

I can then open a terminal window and run the mysqlfailover script:

# mysqlfailover --master=scripts:scripts123@192.168.1.121:3306 --slaves=scripts:scripts123@192.168.1.122:3306 
--candidates=scripts:scripts123@192.168.1.122:3306 --exec-before=/users/tonydarnell/scripts/prefail.sh 
--exec-after=/users/tonydarnell/scripts/postfail.sh --force

Here is a screen shot of the script in action:

To test the script, and to simulate the master server crashing or the mysqld process failing, I will just kill the mysqld process that is on the master server. Since I am using mysqld_safe to start the mysqld process, I will need to kill that process as well.

Once the mysqld processes have been killed, and the mysqlfailover script has refreshed (or you can refresh it manually), the failover process will start. This entire process might take 20-30 seconds (give or take), and you will see something similar to this:

Once the process has completed, the mysqlfailover script will now show you that the failover process has completed and the slave at 192.168.1.122 is now the master.

If you have more than one slave attached to the master, there are options that will allow you to specify a slave to become the master, or you can have the mysqlfailover script decide which slave is the best candidate to be promoted to master. You will need to refer to the mysqlfailover page for more information.

We can check to make sure that our scripts ran successfully by checking the actions of our pre and post-failover scripts. We can check to see if the files were created by our “echo” commands in both scripts:

We can also check our accessWEB file, to see that it has the new connection information.

With the mysqlfailover script, both of our pre and post-failover scripts were executed, and our slave was promoted to the master. Even though the failover process wasn’t immediate (the entire failover process took about a minute), it was successful.

Once the failover has completed, and the old master has been restarted, you can then make the old master (192.168.1.121) a slave to the new master with this command:

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.122',
MASTER_PORT = 3306,
MASTER_USER = 'replicate',
MASTER_PASSWORD = '',
MASTER_AUTO_POSITION = 1;

The mysqlfailover script will recognize the new slave, but now your scripts will not be correct in that it will not copy the master info to the accessWEB file – so you would want to change them to match the new configuration. Of course, you can obviously create scripts that provide the logic to failover to whichever server is available – maybe that is a topic for a future post.

If you prefer to have the old master as the current master, then you can wait until the old master catches up to the new master, stop both servers, and make the old master the new master again. But it is easier to just keep both servers in the new configuration until failover happens again. You could also use the mysqlfailover script to fail over to old master as well – making it the new master again.

 


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.

MySQL Workbench Utilities – Clone MySQL users with mysqluserclone

This post is one in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts.

MySQL Utilities are a part MySQL Workbench. 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. If you don’t have Workbench, you may download the MySQL Utility scripts from launchpad.net. You will also need to install Python and to make sure that your execution $PATH’s are set correctly.

—————————————–

I recently created a new MySQL replication slave instance on a new server, and I needed a way to copy a few of the users from the master database over to the slave database. With the mysqluserclone script, it was fairly easy. The user that I wanted to copy from the master to the slave was named “WebUser”.

Normally, to duplicate a user, I start by taking a look at the privileges that this user has by issuing a “SHOW GRANTS” statement, like this:


mysql> SHOW GRANTS FOR WebUser;
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for WebUser@%                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%' IDENTIFIED BY PASSWORD '*xxxxxxxxxx' |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

I would then go to the new instance, copy the above SQL statement, and issue the CREATE USER command using the HASH value of the password, and then issue the same “GRANT SELECT,…” statement – but without the IDENTIFIED BY PASSWORD part of the command.


mysql> CREATE USER 'WebUser'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for WebUser;
+----------------------------------------------------------------------------------+
| Grants for WebUser@%                                                            |
+----------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%' |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

This doesn’t take that much time, but what if you wanted to create the same user on several remote machines? You would have to connect to each machine, login to mysql, and then issue the commands. With mysqluserclone, the process is much easier and faster.

From the mysqluserclone man page: “This utility [mysqluserclone] uses an existing MySQL user account on one server as a template, and clones it to create one or more new user accounts with the same privileges as the original user. The new users can be created on the original server or a different server.”

As the man page states, you may clone an existing user on the local machine, or on any remote machine. In my case, I wanted to clone a user to a remote machine. You need to make sure that the MySQL user on each machine that you are using to perform the user creation has the proper MySQL permissions on that machine. I have created a user named “utility” on all of my servers, which I use instead of the root user for creating users and executing scripts.

I needed to clone the user named “WebUser” from my master server (192.168.1.2) to my new replication slave server (192.168.1.5). I issued the following command, and this is what happened:


$ mysqluserclone --source=utility:tonyd765@192.168.1.2:3306 --destination=utility:tonyd959@192.168.1.5:3306 \
  WebUser@localhost WebUser:secret1@localhost
# Source on 192.168.1.2: ... connected.
# Destination on 192.168.1.5: ... connected.
ERROR: User WebUser:secret1@localhost already exists. Use --force to drop and recreate user.

At first, it appeared that somehow during the creation of the new replication slave, I had already created the user named WebUser, or that user already existed. I then realized that I had put in the wrong IP address of my new server. In this situation, it was nice that mysqluserclone checked to make sure that the user did not exist before attempting to clone that user. After getting the correct IP address (192.168.1.3), I decided that I wanted to clone the original WebUser (from the master server) to two new users on the new slave server – named WebUser1 and WebUser2.


$ mysqluserclone --source=utility:tonyd32s@192.168.1.2:3306 --destination=utility:tonyd32s@192.168.1.3:3306 \
 WebUser@localhost WebUser1:secret1@localhost WebUser2:secret2@localhost
# Source on 192.168.1.2: ... connected.
# Destination on 192.168.1.3: ... connected.
# Cloning 2 users...
# Cloning WebUser@localhost to user WebUser1:secret1@localhost 
# Cloning WebUser@localhost to user WebUser2:secret2@localhost 
# ...done.

In the above example, the first user “WebUser@localhost” was the user to be cloned, while WebUser1 and WebUser2 are the names of the new users on the remote machine.

I saved the syntax in a shell script, so that the next time I need to clone a user (or users), I can just quickly edit the script and execute it. This makes it a lot easier than having to do everything manually. There are a few more options that you may use with mysqluserclone, and you should reference the official mysqluserclone man page for more information.

Option Description
–help Display a help message and exit.
–destination= Connection information for the destination server in [:]@[:][:] format.
–dump, -d Display the GRANT statements to create the account rather than executing them. In this case, the utility does not connect to the destination server and no –destination option is needed.
–format=, -f Specify the user display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option is valid only if –list is given.
–force Drop the new user account if it exists before creating the new account. Without this option, it is an error to try to create an account that already exists.
–include-global-privileges Include privileges that match base_user@% as well as base_user@host.
–list List all users on the source server. With this option, a destination server need not be specified.
–quiet, -q Turn off all messages for quiet execution.
–source= Connection information for the source server in [:]@[:][:] format.
–verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.
–version Display version information and exit.

Source: http://dev.mysql.com/doc/workbench/en/mysqluserclone.html

 


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.

MySQL Workbench Utilities – Administer MySQL with Python Scripts

Over the next few months, I am going to be writing about the MySQL Utilities, and I will be posting links to each individual blog on this page.

If you haven’t heard of the MySQL Utilities (from the introduction to MySQL Utilities page):

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

If you don’t have Workbench, you may download the MySQL Utility scripts from launchpad.net. You will also need to install Python and to make sure that your execution $PATH’s are set correctly.

The MySQL Utilities are maintained by Chuck Bell. You may find more information about MySQL Utilities on his web site..

To start the MySQL Utilities, from within MySQL Workbench, simply click on the “MySQL Utilities” icon located in the top right area of the home window.

Or, from the MySQL Workbench Plugins menu, select “Start Shell for MySQL Utilities”.

When you open MySQL Utilities, you should be taken to a terminal window, with a list of all of the utilities that are available.

As long as you have your $PATH set correctly, you can just run the scripts from any terminal window and in cron jobs.

Here is a list of the available utilities, with a link to the manual page and a link to my blog about that page (if I have written a post about that utility).

Blog Post Link Script Name & Man Page Description
MySQL Utilities Overview Brief overview of command-line utilities
mut MySQL Utilities Testing
mysqldbcompare Compare Two Databases and Identify Differences
mysqldbcopy Copy Database Objects Between Servers
mysqldbexport Export Object Definitions or Data from a Database
mysqldbimport Import Object Definitions or Data into a Database
mysqldiff Identify Differences Among Database Objects
mysqldiskusage Show Database Disk Usage
Blog mysqlfailover Automatic replication health monitoring and failover
mysqlindexcheck Identify Potentially Redundant Table Indexes
mysqlmetagrep Search Database Object Definitions
mysqlprocgrep Search Server Process Lists
mysqlreplicate Set Up and Start Replication Between Two Servers
mysqlrpladmin Administration utility for MySQL replication
mysqlrplcheck Check Replication Prerequisites
mysqlrplshow Show Slaves for Master Server
mysqlserverclone Clone Existing Server to Create New Server
mysqlserverinfo Display Common Diagnostic Information from a Server
Blog mysqluserclone Clone Existing User to Create New User

 


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.

Using Java to Connect to MySQL Enterprise Monitor with Plugin for Connector/J

If you use MySQL for any number of databases, and if you are not using the MySQL Enterprise Monitor (MEM) to manage your databases, then you are missing out on a great DBA tool.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-introduction.html:

“MySQL Enterprise Monitor is a companion product for MySQL Server that monitors your MySQL instances, notifies you of potential issues and problems, and advises you how to fix the issues. MySQL Enterprise Monitor can monitor all kinds of configurations, from a single MySQL instance that is important to your business, all the way up to a huge farm of database server machines powering a busy web site. “

The query analyzer function of the Enterprise Monitor helps you to analyze/tune your queries. It can tell you which queries are being run the most often, which ones are taking the longest to run, it can even help you find queries that are returning a zero result set, and other query “violators”.

But the caveat is that in order to use the query analyzer function, you have to redirect your MySQL traffic through a proxy server (configured during the MEM installation). Using the proxy server is an additional step that might be acceptible for testing on development servers, but it might not be a great idea in a production environment.

However, if you are using Java for your database connections, you can bypass the proxy server and send your query information directly to the MEM Service Manager. This is accomplished with the MySQL Enterprise Plugin for Connector/J.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-qanal-using-cj.html:

“The MySQL Enterprise Plugin for Connector/J lets you use the Query Analyzer to monitor MySQL queries from any application using the Connector/J JDBC driver. As described in Section 3.2, “Query Analyzer User Interface“, the Query Analyzer can help you locate queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.”

As stated, the information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/J and tweak your Java database connection string, query analysis becomes available for your Java applications. And, the Enterprise Monitor can show you the location of the “offensive” Java code. Here is an example, with the code location highlighted in blue:

There are a few things that you need to do before you can use this plugin. The prerequisites are:

– MySQL Connector/J version 5.1.12 or later.
– JDK-1.5.0 or later.
– MySQL Enterprise Service Manager version 2.2 or later.
– The MySQL instance that the application uses must be monitored by a MySQL Enterprise monitoring agent. The mysql.inventory table must be accessible to the configured user for the Connector/J connection to get the UUID of the monitored server.
– Apache Commons logging in the CLASSPATH of the application being analyzed. If you are not already using Commons Logging, modify the application’s CLASSPATH as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.

I don’t write Java code, but I wanted to be able to test this functionality. I found an example script on MySQL’s web site, and so I was able to modify it to send a test query to my installation of MEM.

I did run into one problem – I wasn’t setting my CLASSPATH variable correctly. To solve this problem, I edited my .profile file for the root user, and added the following JAR file locations to my CLASSPATH: (the colors are added for clarity)


export set CLASSPATH=/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/mysql-connector-java-commercial-5.1.17-bin.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/c-java-mysql-enterprise-plugin-1.0.0.61.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/required/commons-logging-1.1.1.jar:

I am running MySQL on a Mac Pro Server, so you will need to modify the file paths in your CLASSPATH to match your system.

After you have completed the prerequisites, you are ready to see if you can connect to the Service Manager with a query via Java. Here is a sample script that you can use (you will need to modify it your system parameters):


import java.io.IOException;
import java.sql.*;

// Notice, do not import com.mysql.jdbc.*
// or you will have problems!

public class LoadDriver {
   public static void main(String[] args) {

         try {
            // The newInstance() call is a work around for some
            // broken Java implementations

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.2/database_name?"+
               "user=root&password=root_password"+
               "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters"+
               "&serviceManagerUrl=http://192.168.1.2:18080/"+
               "&serviceManagerUser=agent"+
               "&serviceManagerPassword=agent_password");

            //Get a Statement object
               Statement stmt = conn.createStatement();
               ResultSet rs = stmt.executeQuery("SELECT sleep(120)");
               System.out.println("Closing connection");
      

               conn.close();
               } catch (Exception ex) {
               // handle the error
               ex.printStackTrace();
         }
      }
}

You will need to compile the script before running it. Here are the results from compiling and then executing the script: (since you are doing a SELECT SLEEP(120), the script will “sleep” for 120 seconds before finishing.)


macserver01:java root# javac LoadDriver.java
macserver01:java root# java LoadDriver
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using direct result set size methods.
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using batch-aware prepared sql extractor.
Fri Oct 14 14:08:57 EDT 2011 INFO: MySQL Enterprise Plugin for Connector/J version 1.0.0.61 started for MySQL instance 53e71576-026c-4798-961b-0025efc37db2.
Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 14 Oct 2011 18:08:57 GMT

{
    "name": "53e71576-026c-4798-961b-0025efc37db2",
    "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",
    "values":     
{
        
"auto_explain_min_exec_time_ms": 1,
        "capture_examples": "true",
        "capture_explain": "true",
        "enabled": "true",
        "frequency":
null
}
}
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]
Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 14 Oct 2011 18:08:57 GMT

{
    "name": "53e71576-026c-4798-961b-0025efc37db2",
    "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",
    "values":
{
        "auto_explain_min_exec_time_ms": 1,
        "capture_examples": "true",
        "capture_explain": "true",
        "enabled": "true",
        "frequency":
null
}
}
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]

And here are the results as show in the Query Analyzer tab of MySQL Enterprise Monitor:

By clicking on the SELECT sleep(?) link, I can then take a look at the query information:

For more complex queries, this tool really provides a wealth of information to help you tune your queries and increase the performance of your MySQL databases. MySQL Enterprise Monitor has a wealth of great features. For more information, see http://dev.mysql.com/doc/mysql-monitor/2.3/en/index.html.

 


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.

Connecting to MySQL with Perl

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to use Perl to connect to a MySQL database and simply retrieve the database version using an SQL statement. This script has the same functionality as my previous post Connecting to MySQL with Python.

For this example, we will assume that you have installed Perl and MySQL. You will also need to install the DBI and the DBD::mysql modules in Perl. These modules allow you to connect to the MySQL database.

In this script, we are going to use a text file to store our database connection information, as well as a sub-routine to help retrieve our database connection information and to create the connection string for connecting to the database.

Here is the script. (The first line of the script must be the path of your Perl executable – #!/usr/bin/perl.)


- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href=http://mysql.com/>MySQL</a> database
use DBI;
use DBD::mysql;

use warnings;

#----------------------------------------------------------------------
# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# close the accessDB file
close(ACCESS_INFO);
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "SELECT VERSION()";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
@data = $statement->fetchrow_array();
	
# print the first (and only) value from the @data array
# we add a \n for a new line (carriage return)
print "$data[0] \n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

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

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

In the sub-routine “ConnectToMySql” above, we are using a text file (named accessDB) to store our MySQL database name, host name, user name and password (we will call this our access file). In the previous post, we hard-coded the database name, host name, user name and password in the script itself. For this script, we will create a text file to store this information, and we will place the text file in the parent directory (from where the script is stored). I have my sample script in the /cgi-bin/blog/ directory, so we would place the access file in the parent directory /cgi-bin .

The purpose of this access file is to hide the connection information, but it also allows you to quickly change the information if you have a need to switch to a different database, host or user name. And you can use this same file for multiple scripts, but I would have separate scripts for each database name.

Here is a sample access file – which contains (in this order), the database name, host name, user name and password. To match the file name in the sub-routine, we are naming the file “accessDB”. This file should be saved as a text file and make sure that it has the correct read permissions (for Unix, the permission is 644).


- - - - START TEXT FILE - - - - (do not include this line in the file)
my_database_name
192.168.1.1
mysql_user
password
- - - - END TEXT FILE - - - - (do not include this line in the file)

Again, place this file in the parent directory of where your script is located. This prevents anyone from seeing the file in case you didn’t protect the directory contents from being listed via a web page.

We use the sub-routine to extract the database name, host name, user name and password from the file after reading the file into the program. You will place the sub-routine at the end of your Perl script.

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 


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.

Connecting to MySQL with Python

Scripting Python to connect to a MySQL database is fairly simple, even if you don’t have any much experience with Python.

You will need to have Python and MySQL installed. Once you have Python installed, you will need to import the MySQLdb and sys modules. For MySQL, you will simply need a user that has permissions to connect to the database and perform a select statement.

For this example, I am using Python v2.5, MySQL version 5.5.8., with Mac OS X 10.6.8.

Here are the links to download the files and applications necessary for this example script:
MySQL – http://dev.mysql.com/downloads
Python – www.python.org/getit/
MySQLdb module – http://sourceforge.net/projects/mysql-python/

In this post, I am not going to cover installing Python, MySQL or the Python modules, so you will need to do this on your own. There are plenty of other websites and blogs that can assist you with these installations. You will also need to know how to run a Python script on whatever OS you are using.

Once you have everything installed and tested to make sure each application is working properly, you are ready to run this script. This script will simply connect to the MySQL database and fetch the MySQL server version. You will need to substitute your own host IP address, username, password and database name in the script.

Since I am using a unix-based OS, I can simply open up a text editor (or use the vi editor) to write the script. You do not want to save the script in rich-text format, as the script will most likely fail upon execution. You want to save the script as a plain-text file. You will also want to make sure that the line “#!/usr/bin/python” is on the first line of the script.


– – – – START SCRIPT – – – – (do not include this line in the script)
#!/usr/bin/python
# version.py – Fetch and display the MySQL database server version.

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = “192.168.1.1”, user = “username”, passwd = “password”, db = “database_name”)

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute (“SELECT VERSION()”)

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

# print the row[0]
# (Python starts the first row in an array with the number zero – instead of one)
print “Server version:”, row[0]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()

- – – – END SCRIPT – – – – (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 


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

Get every new post delivered to your Inbox.

Join 34 other followers