MYSQL master / Slave setup and rebuilding steps

MYSQL  Replication

MySQL Replication, a solution suggested by our resident database guru, is the process of setting up a master/slave relationship between two database servers, where the slave continually and automatically gets fed all MySQL queries from the master machine, resulting in a replication of data across the two machines.

Once replication has been configured and activated, the slave machine will act as a live MySQL database, holding an up-to-the-minute snapshot of all data from the master machine.

What we love about this solution is that it leaves no "holes". No potential for data loss and no need to ever take the database down.

Once the slave machine is ready, with the flip of a switch, we could redirect all queries to the new machine, terminate the master/slave relationship and we'll be up and running with the new machine.

Here's how the actual implementation went:

Step 1: Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege.

mysql> use mysql;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Step 2: Turn on binary logging of MySQL transactions. The server cannot act as a replication master unless binary logging is enabled. Locate the my.cnf file on the master database server machine and add these two lines to the file, under the [mysqld] section:

[mysqld]
log-bin=mysql-bin
server-id=1

If those options were not present (we had them), you will have to restart the server.

Step 3: Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement - this is important so that we can safely copy the database files, with no fear of the files being changed during the copy operation. Make sure you keep the shell window where you issue the command below open throughout the process, until the tar finishes. Once this window closes, the lock will be automatically released.

mysql> SET GLOBAL WAIT_TIMEOUT=600000; SET WAIT_TIMEOUT = 600000; FLUSH TABLES WITH READ LOCK;

Step 4: Take a snapshot of all data on the master database server. The easiest way to create a snapshot is to use tar. (Make sure you have sufficient storage space on your master server to hold the tar. If unsure, calculate the current size of your database and plan for an additional 70% of available space)

cd /var/db/mysql
tar -cvf /tmp/mysql-snapshot.tar ./ --exclude mysql &

On our system this operation took a little over four hours to complete. Notice the "&" operand - this causes tar to run as a background process. Useful if your shell gets disconnected. You'll know it's done by periodically viewing the process-list using "ps -ax"

Step 5: While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+

The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

Step 6: Now that you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

mysql> UNLOCK TABLES;

Step 7: Hop on the new database server machine (slave). Shutdown the database, then locate the my.cnf file and add these lines to the file:

[mysqld]
server-id=2

Step 8: Copy and extract master database snapshot to the slave server under /var/db

cd /var/db

ftp
open masterdb.com
cd /tmp
get mysql-snapshot.tar
exit

tar --extract --file=mysql-snapshot.tar

Step 9: Start the slave database and issue these commands, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name',
MASTER_USER='replication_user_name',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

Step 10: Start the slave

mysql> START SLAVE;

If everything went well, your slave database will now connect to the master database, downloading all transactions following the time you took the snapshot. From this moment on, the slave database will continually feed itself with data from the master database, always staying in sync.

Confirm the slave server is up-to-date by running a:

mysql> SHOW SLAVE STATUS;

and comparing the position markers with what the master database reports under:

mysql> SHOW MASTER STATUS;

Step 11: Update database-connect functions

Once the slave database is up-to-date, it is now time to update your application code, routing all calls to the new server.

Assuming you have all database access functions encapsulated in a database.php / database.c layer, locate the function that establishes a connection to the database and update the IP address or domain name to point to the slave database.

Voila! There you have it -- migration of MySQL from machine A to machine B without ever taking the database down.

Step 12: Test

Run a tail on the master database MySQL log file, or run SHOW PROCESSLIST and make sure no one is accessing the master database any more. Once confirmed, shutdown the master database and test for any strange side effects.

I recommend you keep the old database server up for a few days until you're absolutely certain the transfer went through smoothly.

Running a mysqlcheck --all-databases on the new database is highly recommended.

MYSQL  Replication rebuild

A few assumptions before getting started:
*You are repairing a previously existing master/slave replication process. If not, you'll have to add a user to Master who the Slave can use to get its info. Will also have to set up some other variables etc. Can find that info somewhere.
*If your server/database is live and are worried about performance, use:
"nice" before linux commands
"--skip-lock-tables" on mysqldumps to prevent locking insert/reads
"nohup ... &" to execute the linux/unix command inthe background, unattended

Here is the process I did:

1) Drop Slave database.
mysql>drop database x;

2) Create dump of Master's create table info:
nohup mysqldump --no-data --skip-lock-tables --single-transaction -uuser -ppassword db > file.sql &

3) Dump any tables you know won't change or that you can stop from changing without problems. Use same command as in 2)

4) **This is the part you need to lock tables
ON MASTER:
mysql>reset master; **This took me ~9 minutes
mysql>flush tables with read lock; **~17 seconds
mysql>show master status;

(VERY IMPORTANT)
Copy down the File and Position information

(From another terminal, still on master's server)
linux>nohup mysqldump --skip-lock-tables --single-transaction --no-create-info --routines --ignore-table=db.table_already_dumped -uuser -ppassword db > file2.sql &

mysql>UNLOCK TABLES;

(If databases are on different servers)
5)Copy dumps to other server
nohup nice scp file1.sql user@IPAddress:/path/ &
nohup nice scp file2.sql user@IPAddress:/path/ &
(Include other dump(s) from step 3) )

(ON SLAVE)
6)Import dumps
mysql>\. file.sql
*Might want to use nohup mysqlimport .... if you want to leave UNATTENDED!

(Still on Slave)
*The x,y on the change master... command are the data you got from "show master status" in step 4)
7)Start replication
mysql>stop slave;
mysql>reset slave; (This will delete the relay logs, etc)
mysql>change master to MASTER_LOG_FILE='x', MYSQL_LOG_POS='y';
mysql>start slave;

mysql>show slave status \G