How to set up mysql master/slave architecture.

转载于我同事David.Yang写的 MySQL主从复制一文,再稍做修改的,原文地址http://blog.chinaunix.net/u/29134/showart_1711722.html

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 -ef | grep mysql | grep -v 'grep' | head -1

For example .
[root@ritto /]# ps -ef | grep mysql | grep -v 'grep' | head -n 1

root      2776  2494  0 03:47 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/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].

vi /usr/local/mysql/my.cnf

# Replication Slave (comment out master section to use this)

server-id = 1

log-bin=mysql-bin

binlog-do-db=db1              //要备份的数据库名1

binlog-do-db=db2             //要备份的数据库名2

binlog-ignore-db=ignore-db1        //排除要备份的数据库1

binlog-ignore-db=ignore-db2        //排除要备份的数据库2

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.

/usr/local/mysql/bin/mysqladmin -uroot -p shutdown          //shutdown

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &      //boot

2. 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.227". The user is "mysql_ms" and his password is "123456".

[root@ritto /]# /usr/local/mysql/bin/mysql -uroot -p

mysql> grant replication slave,file on *.* to 'mysql_ms'@'192.168.4.227' 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.

3. 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.

4. 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=100M --max_allowed_packet=120M db1 > db1.txt

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

[root@ewizchina ~]# /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.

5. What the slave machine want.

a.       User name and password.

b.      Master’s IP address.

c.       Master’s  mysql port.

d.      Master’s  mysql binary log file and position

e.      Master’s backup data.

6. The following is the slave configuration.

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

vi /usr/local/mysql/my.cnf

[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

7. 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/mysql3306.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.3306’,

master_port=3306,

master_user=’mysql_ms’,

master_password=’123456’

master_log_file=’mysql-bin.000004’,

master_log_pos=595;

start slave;