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