MySQL Replication CentOS All Databases

MySQL Replication CentOS All Databases

MySQL Replication CentOS All Databases

MySQL master to slave replication diagram with the MySQL logo on each side

** THIS BLOG WORKS ONLY WITH MySQL VERSION 5.1 or EARLIER **

If you’re going to do it, do it right. Usually blogs have you do this for only 1 Database, that’s pretty much worthless, we’re going to do it right using the traditional Master/Slave Setup. This article assumes you have two Boxes with MySQL already installed, started, just not yet configured. Here’s what we have:

Standard Setup, Port 3306, same MySQL Version 5.1.69

MASTER = 192.168.1.241

SLAVE = 192.168.1.242

Let’s get Started!

Edit the MySQL configuration file on your master (you may want to make a backup)

Running vi /etc/my.cnf on the MySQL master server to add the replication settings

add these lines below the [mysqld] part somewhere

Master my.cnf replication settings with server-id 1, binlog-ignore-db, relay log and log-bin paths

now restart MySQL

Running service mysqld restart on the master so the replication settings take effect

Now we have to create a slave user (service account) in order to process replications, so login to MySQL

Running mysql -u root -p on the master to log in and create the replication user

you prob. do not want to use the example credentials we have:

GRANT REPLICATION SLAVE ON *.* TO the replication user on the MySQL master

now run the next few commands:MySQL FLUSH TABLES WITH READ LOCK and SHOW MASTER STATUS showing the binlog file and position

now we need to write our Position Number down (it’s unique every time, this is our example, yours will be different)

exit to a shell, and dump the database infomysqldump --all-databases --master-data run on the master

and Unlock all tables on Master

Running UNLOCK TABLES on the MySQL master after dumping all databases

now copy/sftp the .db to the SLAVE server.

from here, login and edit the MySQL config

Running vi /etc/my.cnf on the MySQL slave server to add the replication settings

append the following to the config (please edit username/password and host if different)

Slave my.cnf settings with server-id 2, master-host 192.168.1.241, master-user and relay log paths

import your dump (do not yet restart MySQL until after you have imported the DUMP)

Running SLAVE STOP and CHANGE MASTER TO on the slave after importing the master's dump

modify the slave in MySQL

CHANGE MASTER TO on the slave with the master host, replication user, mysql-bin.000001 and position 106
Running slave start and show slave status on the MySQL slave

Perfect! It’s working! (if not check your username/password configuration for replication user in MySQL)

show slave status output with Slave_IO_State Waiting for master to send event and Master_Host 192.168.1.241

Now to show off a bit:

create a database on the Master

Running CREATE DATABASE on the MySQL master to test replication

now let’s go to the slave:

SHOW DATABASES on the MySQL slave listing the test database created on the master

How sweet is that, Seconds Later, the database has been created/replicated to the slave. Beautiful! Just Amazing!

That’s it!

To get a hosted Linux Database Environment visit www.zwiegnet.com/go to get started today!

Related guides: Configure MySQL Multi-Master Replication | Convert MySQL Slave to Master

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *