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

One of my favorite features of MySQL is replication. Replication provides you with the ability to have MySQL automatically copy data from one MySQL instance to another. There are many benefits to using replication, but I just like having an extra copy of my data on another server in case the main server crashes. But if the master crashes, I can then use the MySQL mysqlfailover script to automatically failover from the master to the slave. (see my earlier post – Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication).

MySQL Replication automatically copies the data from the main database (master) to another database (slave). You can have multiple slaves pulling data from a single master, and you can have slaves replicating data off other slaves. If you are new to replication, check out the MySQL Replication FAQ page.

In this post, I will explain how to install or upgrade MySQL (by exporting and importing the data for a “fresh” upgrade), create a master and slave server, and start replication. If you have never installed MySQL or if you have never used replication, then this blog should be able to help you do this without too many headaches. One warning – this is a long post with a lot of details. And, this is not the only way to setup replication. This is just one way to do it. I will show you a different way in part two of this post. In order to try and keep this post as short as possible, I will not explain each command or feature, but instead I will post as many links as possible. This install was performed on a Macintosh with OS version 10.6.8 (for the master) and 10.8.1 (for the slave). This post should apply to most Unix installs, and it should work with Windows as well, with a few modifications.

I currently have one master database with three slaves attached. I will be upgrading from MySQL 5.6.8 to 5.6.9 and all of the servers are GTID-enabled. This post will be relevant if you are able to stop both the master and the slave during the upgrade process – or if you are installing a new master and a slave (without an existing database on either system).

When I upgrade my master server, I like to export the data and do a fresh install, and then re-import my data back into MySQL. Yes, there are ways to upgrade without doing having to export your data. But since I have a relatively small database (<100MB), I like starting with a new server and re-importing the data. This is just my preference.

The first thing that I will need to do is to export my data with mysqldump. I like to export my databases one at a time, in case I have problems with the import, then I can narrow the problem down to a specific database. I need a list of my databases, so from a MySQL prompt, I execute the show databases command.

mysql> show databases; +--------------------+ | Database | +--------------------+ | addressbook | | comicbooks | | genealogy | | information_schema | | inventory | | mysql | | performance_schema | | scripting | | test | | twtr | | website | +--------------------+ 11 rows in set (0.92 sec)

I don’t want to export the four MySQL databases – information_schema, mysql, performance_schema or test – as these will be created in the new install. You are going to want to make sure that you don’t have any activity on your database before you export your data. From a mysql prompt (on the server you want to export data), you can use the FLUSH TABLES WITH READ LOCK command to prevent any additional inserts into the database and to allow all current transactions to be completed.

mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

I then export each database separately (my preference – you could do all of your databases at one time as well with the –all-databases option). In the command below, you will need to change DATABASE_NAME to the actual name of each of your databases:

/usr/local/mysql/bin/mysqldump --user=root --password --quick --skip-opt --create-options \ --add-drop-database DATABASE_NAME > $HOME/mysql_backups/DATABASE_NAME.sql

Here is some information from the mysqldump page that explains each of the options that I used:

--quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. I use this option in case I have problems importing the data, I easily edit the dump file and remove the bad data --skip-opt The --opt option is enabled by default, and --opt is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly. Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt. --create-options Include all MySQL-specific table options in the CREATE TABLE statements. --add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified.

You might want to read the mysqldump page to see which options you will want to use.

Once I have exported my data, I look at the export files to make sure that they were created. You might even want to open one of the smaller dump files (if the file isn’t too large) in a text-editor and just take a look to make sure everything looks good. If you used the same commands for each dump, then the smaller file will show you if the export is in the format you want.

If you have an existing MySQL database, you can also export the user and grant information so you can import this back into the new database. See my last blog post “Retrieving List of MySQL Users and Grants with Perl” to find out how to export your users and grants. You don’t have to use Perl to do this – you can manually use the commands explained in the post.

Now you can shutdown your MySQL instance.

Once the database has shutdown, you can now install the new version of MySQL. In this case, I am installing version 5.6.9. If you are on Unix or Mac, and you are installing as root, you will need to change ownership to the mysql user of the files in your home MySQL directory after the install process.

After you install the new database, you will want to execute the mysql_install_db script. You can also refer to the post-installation procedures on the MySQL web site. Start MySQL, and run the script:

root@macserver01: # ./scripts/mysql_install_db Installing MySQL system tables...OK Filling help tables...OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h macserver01 password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file ./my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as ./my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server

If you ran this script as root, you will need to change the ownership of the mysql-bin and mysql-bin.index files in the mysql data directory to the mysql Unix user.

If you intend to use this server as a master server, you will need to edit the my.cnf (my.ini on Windows) file to make it ready to be a master server. The minimum you must change is to add these lines under the [mysqld] section of your my.cnf option file.

log-bin=mysql-bin server-id=1

The “server-id” must be unique to each server. I usually set my master server-id = 1. Check out the Setting the Replication Master Configuration page on the MySQL web site to make sure you have the correct settings for your server.

We will be using global transaction identifiers (GTID) for replication. GTID’s are a new replication feature as of MySQL 5.6.5. To enable GTID, you will need to add these lines under the [mysqld] section of your my.cnf option file.

gtid_mode=ON enforce-gtid-consistency log-bin log-slave-updates binlog_format=mixed

Now you can start the MySQL server (if it isn’t already started). When you executed the mysql_install_db script, it created the grant tables. You are going to want to change the root password and delete any anonymous accounts. See Securing the Initial MySQL Accounts for specific information for your operating system.

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

$ ./bin/mysqladmin -u root password 'new-password'

Right after you change the root password, you will want to test the new root password by logging in with mysql as root at a Unix prompt:

root@macserver01: $ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2259 Server version: 5.6.9-rc-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Before we perform any more transactions, we need to make sure that we have GTID enabled. To see if GTID has been enabled, we can execute this statement from the mysql prompt:

mysql> show global variables like '%GTID%'; +--------------------------+-----------------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------------+ | enforce_gtid_consistency | ON | | gtid_executed | 82F20158-5A16-11E2-88F9-C4A801092ABB:1-26 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +--------------------------+-----------------------------------------------+ 5 rows in set (0.00 sec)

The variables enforce_gtid_consistency and gtid_mode should have the value of “ON”. The variable value for gtid_executed shows the UUID of the server, and the 1-26 shows that transactions one through 26 were executed. See GTID Concepts for more information about this value. Now that we know GTID has been enabled and our root password has been changed and confirmed, we can continue with deleting the anonymous accounts, creating our users and importing our data.

To find and delete the anonymous accounts, from a mysql prompt:

mysql> use mysql; Database changed mysql> SELECT user, host FROM user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | | macserver01.local | | root | macserver01.local | | | localhost | | root | localhost | +------+-----------------------+ 6 rows in set (0.00 sec)

The users that are blank are anonymous users. You can double-check the blank users with this statement:

mysql> select user, host from user where user = ''; +------+-----------------------+ | user | host | +------+-----------------------+ | | VM-Mac-1081-128.local | | | localhost | +------+-----------------------+ 2 rows in set (0.00 sec)

You may now delete the blank users:

mysql> delete from user where user = ''; Query OK, 2 rows affected (0.00 sec)

These are the users that are remaining:

mysql> select user, host from user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | root | VM-Mac-1081-128.local | | root | localhost | +------+-----------------------+ 4 rows in set (0.00 sec)

You can now create the users that you exported from your previous instance, or if this is a new install, you may create the users that you think you will need for this instance. If you exported the users, then you will want to remove the “CREATE USER” statement for the root users that match the user and host values above. If you had grants for these users that were different than the default grants, you can still execute the grant statements.

If you are installing MySQL for the first time on your master or if you did not have a replication user in your previous instance, you will need a replication user for replication. See Creating a User for Replication for more details, but here is a sample replication user creation statement:

mysql> CREATE USER 'replicate'@'%.mydomain.com' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%.mydomain.com';

Now that our users have been created, we can import the data from our earlier export. If this is a new install, then you may skip this step. Before we import the data, let’s look at the master status. We will use this information later, so save the output to a text file.

mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000006 Position: 71046480 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-26 1 row in set (0.00 sec)

We can use the mysql program to import the data from the backups that we created earlier. You will need to execute this command for each database backup file:

mysql -uroot -p DATABASE_NAME < $HOME/mysql_backups/DATABASE_NAME.sql

Once you have imported the data, you can check the master status to see how many transactions were executed. Since I exported my data with one insert statement per line, the total number of insert statements that I had in my import should be close to the number of transactions that were executed.

mysql -uroot -p DATABASE_NAME show master status\G *************************** 1. row *************************** File: mysql-bin.000006 Position: 71046480 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162551 1 row in set (0.00 sec)

The value of Executed_Gtid_Set contains the same type of information from the variable gtid_executed that we looked at previously. The value 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162551 contains the UUID of the server and shows that transactions one through 162551 have been executed on this new instance. Since we exported the original data with each data row on an individual INSERT line, we can now figure out how many rows of data we imported. Before we imported the data, the value of Executed_Gtid_Set was 82F20158-5A16-11E2-88F9-C4A801092ABB:1-26 – so we executed 26 transactions before the data import. The difference in the total number of rows from the data import less the number of transactions that were executed earlier, minus the total number of any other statements (such as CREATE TABLE) should give us a count of the number of lines of data we imported. This step isn’t really necessary, but I like to do it just to make sure that I didn’t lose any data. And yes, it might be overkill.

We can do a line count for all of the data files that we imported earlier that contained an “INSERT” statement. This will give us a count of the total number of inserts from our import.

$ ls -l $HOME/mysql_backups/*sql total 61832 -rw-r--r-- 1 root staff 1151006 Jan 9 00:12 addressbook.sql -rw-r--r-- 1 root staff 492652 Jan 8 23:11 comicbooks.sql -rw-r--r-- 1 root staff 27485322 Jan 8 23:11 genealogy.sql -rw-r--r-- 1 root staff 603943 Jan 8 23:11 inventory.sql -rw-r--r-- 1 root staff 779634 Jan 8 23:11 scripting.sql -rw-r--r-- 1 root staff 1077248 Jan 8 23:11 twtr.sql -rw-r--r-- 1 root staff 50643 Jan 8 23:11 website.sql $ grep INSERT *sql | wc -l 162444

Now we can get the total number of CREATE statements:

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

The total number of transactions executed so far is 162551. If we subtract the number of INSERT lines from the import (162444) and CREATE statements (81), we get the total of transactions that had taken place before the data import, which was 26. We can now confirm that all of our data was imported successfully. We can now install MySQL on the slave and start replication.

For the slave, we will want to do the same steps for the install process as we did on the master, but we will stop at importing any data. Also, we will not have to create our additional users (with the exception of the replication user) and we will not have to import any data. Once we turn on the slave instance, the users will be replicated and the data we imported will be copied to the slave. Here are the steps:

• Install MySQL version 5.6.9 (change ownership of the files in the mysql directory to mysql if you installed as root)
• Run the post-install script mysql_install_db (change ownership of the mysql-bin and mysql-bin.index files in the data directory if you installed as root)
• Change the root password and test it.
• You don’t have to remove the anonymous accounts, as the SQL statements that we performed on the master will also be executed on the slave.
• Create the replication user.
• Shutdown the mysql server.
• Edit the my.cnf options file (my.ini on Windows) and insert the GTID variables as shown earlier. But, the server-id value must be something other than the value from the master server. You can set this value to 2.
• Start the server
• Test to make sure GTID is enabled.
• Stop the slave by logging into mysql as root and executing “stop slave;”

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

We can now provide the slave with the information on which master to use. Before GTID, you would have to tell mysql which binary log you wanted to use, and the position within that binary log. With GTID, you only need to set MASTER_AUTO_POSITION = 1“;

mysql> CHANGE MASTER TO -> MASTER_HOST = '', -> MASTER_PORT = 3306, -> MASTER_USER = 'replication_user_name', -> MASTER_PASSWORD = 'replication_user_password', -> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 2 warnings (0.20 sec)

You will need to change the replication_user_name and replication_user_password to match the values you used when you created the replication user.

Before we turn on the slave, you may check the status of the slave:

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.2 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: WEB_SERVER_01-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 151 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2 1 row in set (0.00 sec)

We can check the values for Master_Host and Master_User to make sure they match our master server. Since the slave hasn’t been started yet, the value for Retrieved_Gtid_Set is blank – as we haven’t retrieved any data from the master. Once we start the slave, the value for Retrieved_Gtid_Set will show us how many transactions have been retrieved from the master. The value for Executed_Gtid_Set shows that we have executed two transactions on this new slave instance – changing the root password and creating the replication user. Now we can start the slave:

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

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

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.2 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 1914 Relay_Log_File: WEB_SERVER_01-relay-bin.000007 Relay_Log_Pos: 1024 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'replicate'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replicate'@'%' IDENTIFIED BY PASSWORD '*BE1BDEC0AA74B4XCB07X943E70X28096CXA985F8'' Skip_Counter: 0 Exec_Master_Log_Pos: 814 Relay_Log_Space: 71051295 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1396 Last_SQL_Error: Error 'Operation CREATE USER failed for 'replicate'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replicate'@'%' IDENTIFIED BY PASSWORD '*BE1BDEC0AA74B4DCB079943E70528096CCA985F8'' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 82f20158-5a16-11e2-88f9-c4a801092abb Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 130109 21:50:45 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562 Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3, C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2

We can see from the value for Retrieved_Gtid_Set is 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562, which shows that we have already retrieved all of the transactions from the master. The Executed_Gtid_Set value of 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3 shows that we have processed the first three transactions from the master, and the value of C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-2 shows we have executed two transactions from the slave.

The variable Last_Error shows that we also have an error because the CREATE USER statement for the replication user from the master database fails on the slave, as we have already created a replication user. I purposely created the same user to show you what happens when you have an error on the slave. In the above list of installation actions to do on the slave, you can skip “create replication user”.

To skip this error and continue with replication, you can set the SQL_SLAVE_SKIP_COUNTER to “1”, which tells the slave to skip one transaction. You must stop the slave, set SQL_SLAVE_SKIP_COUNTER = 1 and restart the slave.

mysql> stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;start slave; Query OK, 0 rows affected (0.16 sec)

Let’s look at the slave status again: (the output is truncated as we only need to look at the values for Retrieved_Gtid_Set and Executed_Gtid_Set)

mysql> show slave status\G .... Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562 Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-15401, C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-12 ....

You can see that the value of the Executed_Gtid_Set has changed to 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-15401, which means that the slave is starting to process the transactions from the master, and is on transaction 15401. You can also see that transaction number four was skipped (when we executed the SET GLOBAL SQL_SLAVE_SKIP_COUNTER command). This was the transaction to create the replication slave user.

You can check the slave status until you see that all of the transactions that were retrieved from the master have been completed on the slave:

mysql> show slave status\G .... Retrieved_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562 Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-3:5-162562, C242A198-5AAA-11E2-8CC0-387DCB822A4B:1-12 ....

You can go back to the master and see the master’s status:

mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000007 Position: 1914 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 82F20158-5A16-11E2-88F9-C4A801092ABB:1-162562 1 row in set (0.00 sec)

We can now see that the value for Executed_Gtid_Set on the master is the same as the value on the slave (not including the transactions executed on the slave itself). So, the slave now has the same data as the master, and it is up to date and not lagging behind the master. If you have a busy master server, your slave might lag behind while it updates the records on the slave.

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

SLAVE:

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

MASTER:

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

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

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

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

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

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Advertisement