Mysql主从集群部署

最后整个部署完，参考最多的，是这篇文章： http://centoshelp.org/servers/database/mysql-database-replication/

Doing the Work
This assumes you have MySQL currently installed on both machines and have at least one database you wish to replicate. We’ll be using the database “ mydb ” as an example, you’ll need to change this to your actual database. We’ll assume the IP of the master MySQL server is 192.168.0.6

【前两步是配置主数据库的配置文件，在mysqld的属性下添加下面几行，如果添加到其它属性行，则无效。这里和教程的有点不一样，可是我按照这样的配置发现行得通，三行分别代表：数据库名，编号（1是主，大于1是从），二进制日志所处文件夹（如果二进制日志没有打开，就要百度一下怎么打开）  

binlog-do-db = mydb 

server-id = 1 

log-bin = mysql-bin 】

Configuring the Master:
First we have to edit /etc/my.cnf . So we have to enable networking for MySQL, and MySQL
should listen on all IP addresses, uncomment or add these lines ( note: # is a comment ):
this is a comment --> #skip-networking
should look like this:
skip-networking
bind-address = 127.0.0.1

 Next, we need to tell MySQL where to write the logs in /etc/my.cnf:
These logs are used by the slave to see what has changed on the master, which log file it
should use, and we have to specify that this MySQL server is the master. We want to replicate
the database mydb , so we put the following lines into /etc/my.cnf:
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=mydb
server-id=1

 Restart MySQL daemon:
]# service mysqld restart

 Login to MySQL shell as the root user:
]$ mysql -u root -p

【下面虽然只运行了一句话，可是我这样做之后发现在从数据库压根就特么运行不了，于是百度了一下，有人说还要运行下面这几行，我试过之后就可以了。

mysql> grant RELOAD on *.* to 'slave_user'@'%' identified by '';

mysql> grant SUPER on *.* to slave_user@'%' identified by '';

mysql> grant REPLICATION CLIENT on *.* to slave_user@'%' identified by '';】

Now that we’re logged in as the root user we need to create a user with database replication privileges:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '';
mysql> FLUSH PRIVILEGES;

【下面这个表里面的东西一定要记住啊，里面是你二进制log的文件名，还有当前行数，之后从数据库开始同步的时候，从哪里开始，就要靠这个了，最好截图保存下来。我就是前一天不知道这几行的意思，第二天发现值都不一样了，可是错过了才发现根本回不去啊！！】

Next, while still logged into the MySQL shell:
mysql> USE exampledb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Which should give us output similar to this:
+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 167 | mydb | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
( note: copy this information, we'll need it for the slave setup later. )
Also, DO NOT logout of the MySQL shell after "FLUSH TABLES WITH READ LOCK;" or you will
lose the lock.

【又一个坑啊，这里虽然说有一个  LOAD DATA FROM MASTER;  方法号称更简单，可是我第一次弄到最后才发现这货已经被高版本的Mysql给遗弃了，官方推荐另外dump的方式啊！！推荐完全不要看这个弃子！ 】

 While still logged in to the MySQL shell, open another terminal on the system ( not a MySQL shell ):
There are two possibilities to get the existing tables and data from mydb from the master to
the slave. The first one is to make a database dump, the second one is to use the:
LOAD DATA FROM MASTER; command on the slave. 

The latter has the disadvantage the the database on the master will be locked during this 
operation, so if you have a large database on a high-traffic production system, this is not
what you want, and recommend to follow the first method in this case. However, the latter
method is very fast, so both will be described here.

【推荐方法就是这个方法一，这里要做的就是用下面这条命令把要做集群的数据库备份出来，得到mydb.sql文件】
Method #1
]# mysqldump -u root -p --opt mydb > mydb.sql
Important: There is no space between -p and )
alternatively: If you leave out '-p' mysqldump will prompt you for it before
executing your command.

This will create an SQL dump of mydb in the current working directory with the filename:
mydb.sql . Transfer this file to your slave server.

Method #2
If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do
at this moment.
 Now, go back to the open MySQL shell and issue the following MySQL commands:
mysql> UNLOCK TABLES;
mysql> FLUSH PRIVILEGES;
mysql> exit;

This concludes the MySQL master setup.【好，主数据库的操作，就是这样，谢谢！】

 Configuring the MySQL slave, login to the MySQL server as root on the slave system:
]$ mysql -u root -p
( note: this command assumes you are not root on the system, but should work even if you are.)
Create the database “ mydb ” on the slave MySQL server:
mysql> CREATE DATABASE mydb;【先创建要做主从集群的数据库，只要个名字就可以了】
mysql> exit;

【这里刚开始看还有点玄乎，直接跨机器同步啊，这么屌！后来发现只是把上面生成的备份文件，用FTP拷到这台机器来，再恢复……】

 If you have made an SQL dump of  mydb  on the master and have transferred it to the slave, then it is now time to import the SQL dump into our newly created mydb  on the slave::
]# mysql -u root -p mydb < /path/to/mydb.sql
Important: There is no space between -p and )
alternatively: If you leave out '-p' mysqldump will prompt you for it before
executing your command.

 Next we have to tell MySQL on the slave that it is the slave, that the master is  192.168.0.6 , and that the master database to watch is  mydb . Therefore we add the following lines to  /etc/my.cnf :
Add these lines in /etc/my.cnf on the slave server:【和主数据库一样，配置文件里面加这几行，这里没有问题，直接这样加到mysqld下面】

server-id=2
master-host=192.168.0.6
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=mydb
Restart the MySQL server on the slave:
]# service mysqld restart【重启生效】

 If you have not imported the master  mydb  by using an SQL dump, but want to go the  LOAD DATA FROM MASTER;  way, then it is now time to get the data from the master  mydb ::
login to the MySQL shell:
]$ mysql -u root -p
( note: this command assumes you are not root on the system, but should work even if you are.)

once logged in:
mysql> LOAD DATA FROM MASTER;
mysql> FLUSH PRIVILEGES;
( note: stay logged in to the MySQL shell )

【最后，根据主数据库的二进制log，从数据库不停的模拟操作，从而达到复制效果】

 Final steps (still logged in to MySQL shell):
mysql> SLAVE STOP;

( note: make sure to change the example values here appropriately. )
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.6', MASTER_USER='slave_user', MASTER_PASSWORD='', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=167;
mysql> START SLAVE;
mysql> FLUSH PRIVILEGES;
mysql> exit;

* MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.6).
* MASTER_USER is the user we granted replication privileges on the master.
* MASTER_PASSWORD is the password of MASTER_USER on the master.
* MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
* MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Troubleshooting
How to test.
Now query your database and compare the databases on both systems and make sure replication is taking place:
Login to the MySQL shell on both servers like we did above, and issue these commands:

mysql> use mydb;
mysql> select * from queries order by querie_id desc limit 200;

You should see a list of the last 200 queries to the database. Compare the output from both
servers and it should match exactly.
Make sure MySQL is running on both systems:
]# chkconfig --list | grep mysql; service mysqld status