4,348
edits
MySQL: Difference between revisions
→Setting up a Replication (Master-Slave)
→Get master's binary log coordinate: Create a snapshot using mysqldump |
|||
| Line 2: | Line 2: | ||
== Setting up a Replication (Master-Slave) == | == Setting up a Replication (Master-Slave) == | ||
* ''Last tested on Ubuntu 14.04.05 LTS (trusty) as the master, and Ubuntu 16.04.02 LTS (xenial) as the slave.'' | |||
=== Setting the replication master config === | In this procedure, the master is assumed to have tables with InnoDB with data. Some parameters and commands are specifically for InnoDB engines. My master happens to be MySQL and the slave happens to be running MariaDB. This shouldn't cause any issues as MariaDB is a drop-in replacement for MySQL. | ||
The slave is a newly installed Ubuntu server installation. | |||
=== Setting the replication master config (on master) === | |||
Activate the binary log on the master. <code>innodb_flush_log_at_trx_commit</code> and <code>sync_binlog</code> added for greatest possibility durability and consistency in a replication setup using <span class="package">InnoDB</span> with transactions. | Activate the binary log on the master. <code>innodb_flush_log_at_trx_commit</code> and <code>sync_binlog</code> added for greatest possibility durability and consistency in a replication setup using <span class="package">InnoDB</span> with transactions. | ||
| Line 16: | Line 20: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== Create a user for replication === | === Create a user for replication (on master) === | ||
Grant <span class="package">REPLICATION_SLAVE</span> privilege to the new user. | Grant <span class="package">REPLICATION_SLAVE</span> privilege to the new user. | ||
| Line 25: | Line 29: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== Get master's binary log coordinate === | === Get master's binary log coordinate (on master) === | ||
On the master stop commit operations on InnoDB table and read the coordinates. | On the master stop commit operations on InnoDB table and read the coordinates. <code>FLUSH TABLES WITH READ LOCK;</code> command needs to be run in one session and left at that. Running any other commands after will potentially unlock the tables again. | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
mysql> FLUSH TABLES WITH READ LOCK; | mysql> FLUSH TABLES WITH READ LOCK; | ||
<syntaxhighlight/> | |||
Open another terminal session on the same server, and get the binary log position. Write down the value for File and Position somewhere. In this example, it would <span class="package">mysql-bin.000003</span> and <span class="package">73</span>, respectively. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> SHOW MASTER STATUS; | mysql> SHOW MASTER STATUS; | ||
+------------------+----------+--------------+------------------+ | +------------------+----------+--------------+------------------+ | ||
| Line 39: | Line 48: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== Create a snapshot using mysqldump === | === Create a snapshot using mysqldump (on master) === | ||
This | While the first session with <code>FLUSH TABLES</code> is still hanging there, run the following command on a different session to dump the databases. This assumes there are existing data using InnoDB engines. | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
$ mysqldump --all-databases --master-data > dbdump.db | $ mysqldump --all-databases --master-data > dbdump.db | ||
</syntaxhighlight> | </syntaxhighlight> | ||
After this is done, you can unlock the tables again. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> unlock tables; | |||
</syntaxhighlight> | |||
Copy <code>dbdump.db</code> to the slave server using scp or whatever else you use for server-to-server file copying. | |||
=== Setting the replication slave configuration (on slave) === | |||
This procedure can be applied to multiple slave servers. Just make sure that you have different <span class="package">server-id</span> value for each slave server. You may also want to turn binary logging on for the slave if you're planning to switch off the master in the future and turn the slave into a new master for other slaves. | |||
The file can be located as <span class="path">/etc/mysql/mysql.conf.d/mysqld.cnf</span> or <span class="path">50-server.cnf</span>. | |||
<syntaxhighlight lang="cfg"> | |||
[mysqld] | |||
server-id=2 | |||
</syntaxhighlight> | |||
Restart mysql daemon. On mine, the command is <code>systemctl restart mysql.service</code>. Same with MariaDB, if you have the full package installed. | |||
=== Setting the master config on the slave (on slave) === | |||
Configure the slave with necessary connection information. Make sure MASTER_LOG_FILE and MASTER_LOG_POS match what you had written down above EXACTLY. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> CHANGE MASTER TO | |||
-> MASTER_HOST='<em>master_host_name_or_ip_address</em>', | |||
-> MASTER_USER='repluser', | |||
-> MASTER_PASSWORD='mypassword', | |||
-> MASTER_LOG_FILE='mysql-bin.000003', | |||
-> MASTER_LOG_POS=73, | |||
-> MASTER_CONNECT_RETRY=10; | |||
</syntaxhighlight> | |||
=== Load the existing data on the slave === | |||
Replace <code>~/dbdump.db</code> with the full path to the same file. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> source ~/dbdump.db | |||
</syntaxhighlight> | |||
=== Start the slave replication process === | |||
Once loading of the data completes, you can start the slave replication. | |||
<syntaxhighlight lang="mysql"> | |||
mysql> start slave; | |||
</syntaxhighlight> | |||
== Checking the status of the replication setup == | |||
If you're interested in checking the status, run <code>show slave status;</code> on the slave. | |||
On the master, you can run <code>show master status;</code>, or <code>show processlist</code> to check the running processes. | |||
== List all database without the border == | == List all database without the border == | ||