How to set up mysql master/slave architecture.

All the info below should be added in /etc/my.cnf. If you can’t find this file, just use the following statements to find where it is.

1. To find the location of my.cnf.

ps aux | grep mysql | grep -v ‘grep’ | head -n 1

For example .

[root@localhost ~]# ps aux | grep mysql | grep -v ‘grep’ | head -n 1

root      2952  0.0  0.2   4512  1220 ?        S    16:12   0:00 /bin/sh ./mysqld_safe –defaults-file=/usr/local/mysql/my.cnf

Then we know the correct configuration file is /usr/local/mysql/my.cnf.

We should add the following lines to my.cnf in section [mysqld].

server-id = 1

log-bin=mysql-bin

binlog-do-db=db1

binlog-do-db=db2

binlog-ignore-db=ignore-db1

binlog-ignore-db=ignore-db2

expire_logs_days = 5

max_binlog_size=500M

log-slave-updates

Then restart mysqld manually and execute the following statements in mysql command line client.

1. To grant valid user to slave.

To assume this thread called A.

grant file,replication slave on *.* to ‘mysql_ms’@’Slave IP’ identified by ‘Your password’;

flush privileges;

For example, if my slave’s ip address is "192.168.4.55". The user is "mysql_ms" and his password is "123456".

mysql> grant replication slave,file on *.* to ‘mysql_ms’@’192.168.4.55’ identified by ‘123456’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Be sure to send the username and password to me.

1. Lock tables and get the master’s binary log file and position.

flush tables with read lock;

show master status\G

You must not quit the current mysql command line client.

mysql> show master status\G

*************************** 1. row ***************************

            File: mysql-bin.000004

        Position: 595

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

mysql>

Then send the results to me.

1. Backup exact database.

Now we locked all the tables and got the exact binary log file and position in the previous step. So begin to backup the exact database right now.

Use mysqldump to dump the necessary data to flat file, then use tool named gzip or gzip2 to compress it.

For example, if your database name is db1 and your mysql installation path was added in the environment variable named "PATH".

Use the following statement to backup your databases’ data.

mysqldump -uroot -p –net_buffer_length=10M –max_allowed_packet=11M db1 > db1.txt

Here is my example in my machine, my database name is test.

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p –net_buffer_length=100M –max_allowed_packet=120M test > test.txt

To compress the flat text file to a gzip file, using the following command.

gzip db1.txt

Then the compressed file named db1.txt.gz will be generated. You should send this file to me.

After all the above completes, go to thread A and execute the following command.

unlock tables.

Then quit thread A.

1. What the slave machine want.

User name and password.

Master’s IP address.(192.168.4.54)

Master’s  mysql port.(3308)

Master’s  mysql binary log file and position

Master’s backup data.

1. The following is the slave configuration.

Add the my.cnf on slave machine and restart mysqld.

[mysqld]

server-id = 2

replicate-do-db=db1

replicate-do-db=db2

replicate-ignore-db=ignore-db1

replicate-ignore-db=ignore-db2

log-bin=slave-bin

1. Get the master information on slave machine.

Then exit from it and use the following command to import from the backup file in the shell environment.

gzip gb1.txt.gz

mysql –uroot –p –S/tmp/mysql3307.sock < gb1.txt

Enter the mysql command line client.

set @@global.max_allowed_packet=11*1024*1024;

Then exit it and enter it again.

Change master to

master_host=’192.168.4.54’,

master_port=3308,

master_user=’mysql_ms’,

master_password=’123456’

master_log_file=’mysql-bin.000004’,

master_log_pos=595;

start slave;