来源：【原创】How to set up mysql master/slave architecture. (2008-12-10 09:55)
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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- 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;
