MySQL slave on CentOS

Got a CentOS box running as a slave of my Ubuntu server.

Here are the steps I took, starting on the slave:

# yum install mysql-server*

Then we ought to secure our installation:

# /usr/bin/mysql_secure_installation

On the master, we need to create a user for the slave to connect as (adjust user name & IP address to suit your needs):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.%' IDENTIFIED BY PASSWORD [your-choice-of-password]

Now, as extra precaution, lock all tables for reading only (our mysqldump will --lock-all-tables too):

mysql> FLUSH TABLES WITH READ LOCK;

That will lock out writing to the tables (all tables, all databases) until while we get a dump of the data. Do not close this mysql session else the locks will be released. When running mysqldump with the "--master-data", it will set "--lock-all-tables" there too, so this might be a bit redundant.

I like to dump the databases individually, so I use a script for that, but here is how to simply dump all of them (run in a Bash shell):

$ mysqldump --create-options --master-data=1 --hex-blob --tz-utc --add-drop-database --disable-keys --flush-logs --add-locks --no-autocommit --all-databases > load-on-slave.sql

Now we can release the lock on the master:

mysql> unlock tables ;

On the slave, we need to get our dumped data:

ftp [master host]
ftp> bin
ftp> prompt
ftp> mget load-on-slave.sql
ftp> quit

We also need the slave's /etc/my.cnf configuration file set to slave mode:

/etc/my.cnf:

master-user = [slave's user name]
master-password = [slave's password]
master-port = 3306

Nearing completion, start mysqld on slave, then issue a CHANGE-MASTER-TO statement setting the master_host (adjust to your needs), master_user (the user created on master, above), and master_password:

mysql> CHANGE MASTER TO master_host='192.168.1.##', master_user='slave', master_password='[password]';

Finally, load the data into the slave and start the slave, password option omitted in this example; you might need it:

In Bash:

$ mysql --user=[you]

Now, initiate the slave process:

myql> start slave;

See if things appear to be working:

mysql> show slave status \G

You can now create a database on the master "CREATE DATABASE ZZZ;", then "SHOW DATABASES;" on the slave to see if it appears in the list. A "DROP DATABASE ZZZ;" should make the ZZZ database disappear from the master and the slave.

Further details on master-slave replication on version 5.1 is available on MySQL's web site (or, for version 5.5 here.)

Tags: