#configuring mysql server

We've written before about installing and configuring MySQL server as well as touched on creating reserve backups of your databases. Creating a reserve copy is probably the simplest process to understand an implement for a budding sysadmin, and is a great place to start getting used to the basic objectives of MySQL server administration.

However, when encountering real world problems in your daily routines, the simple solutions quickly become inadequate. Creating a basic reserve copy of a MySQL database with the help of the "mysqldump" command on your production server works fine while the database is small and used sparingly. Once the database sizes start reaching territories of several gigs, though, reserve copy creation will start not only taking up significant amounts of time, but will also significantly impact server performance. In order to retain consistency of the copy, reserve copy needs to be run in one transaction; if the database is being written to while it's being copied, the hit to performance will be even more severe due to it actively writing to the transaction log.

In this article, we'll start with a simple setup of MySQL replication in a "master/slave" configuration. In this case, the primary MySQL server (master) will save all queries leading to changes in the database into binary logs that will, in turn, be sent over to replication servers (slaves) that are setup as read-only.

With this setup it's much easier to pause replications of a slave server to make a reserve backup- even of larger databases. Then, after the backup process has finished, all changed data will be synced to the slave server automatically.

You can use the slave servers to reduce the load off the master server, handling the requests that don't make changes to the database (like the SELECT queries, assuming that replication has gone through without getting delayed and the information has been synchronized.) You could even launch a really resource intensive request on a slave server, so as to not encumber the master server. Bottom line, this setup offers a lot of flexibility.

You can also set up replication using master-master mode or use a complete MySQL Galera cluster mode that can organize multiple servers in a multi-master configuration. I'm hoping to cover these configurations in future articles, so stay tuned for those.

Configuring Master-Slave Replication

To set this up, you'll need two servers that both have MySQL server installed. For our examples we'll be using a database called "test".

Configuring Master Server

1. We're going to insert settings into "etc/mysql/my.cnf" file on the master server:

server-id=1 log-bin=/var/lib/mysql/mysql-bin replicate-do-db=test

2. Now we'll add a user for replication:

mysql –h localhost –uroot –p mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'somepass'; mysql> GRANT REPLICATION SLAVE ON test.* TO 'repl'@'%';

Then restart the MySQL service:

root@mysql-master# service mysql restart

3. Let's check server status:

mysql> SHOW MASTER STATUS\G *************************** 1. row ***************************            File: mysql-bin.000002        Position: 107    Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

This reflects the status in realtime, and as the database grows, so will the number in the "Position" field.

Configuring Slave Server

1.  Set up the "etc/mysql/my.cnf" file:

server-id = 2 relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index replicate-do-db = test

Restart the MySQL service:

root@mysql-slave# service mysql restart

2. Temporarily block MySQL from writing to the "mysql-master" server, and let's look at the current status in the binary logs. ATTENTION: make sure you do this step during maintenance and not when it's in use, as it won't be possible to perform database operations during this time:

mysql@mysql-master> FLUSH TABLES WITH READ LOCK; mysql@mysql-master> SET GLOBAL read_only = ON; mysql> SHOW MASTER STATUS\G File: mysql-bin.000002 Position: 107

3. Let's make a database dump using the "mysqldump" command, as well as remove the write block:

root@mysql-master:~# mysqldump test --password --user=repl > test.sql mysql@mysql-master> SET GLOBAL read_only = OFF;

4. Recreate the database on the "mysql-slave" server:

root@mysql-slave:~# mysql –h localhost –uroot –p < test.sql

5. Finally, intialize replication:

mysql@mysql-slave> CHANGE MASTER TO MASTER_HOST = "", MASTER_USER = "repl", MASTER_PASSWORD = "somepass", MASTER_LOG_FILE = "mysql-bin.000002 ", MASTER_LOG_POS = 107; mysql@mysql-slave> start slave;

6. Let's check the status of the replication:

mysql> show slave status\G

From what this command returns, these are the lines we want to look at:

              Slave_IO_State: Waiting for master to send event                   Slave_IO_Running: Yes            Slave_SQL_Running: Yes        Seconds_Behind_Master: 0

"Seconds_behind_master" refers to how far the slave server is running behind the master server. Ideally this should return a 0. If you see this number increase, the lag is likely the result of a limited amount of resources available for replication. Check out this article for troubleshooting issues like these.

If you didn't run into any issues, you should be all done and have your servers set up for realtime replication. If you're up for experimenting, you can modify or delete your tables on the master server and see if those changes are reflected in the slave.

That should be it for this article. Check us out on Facebook and @serversuit to stay up to date with our articles, and look out for my follow up on master-master setups in the near future! Until next time!