Mysql主从集群部署

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

Doing the Work
This assumes you have MySQL currently installed on both machinesand have at least one database you wish to replicate. We’ll beusing 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 is192.168.0.6

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

binlog-do-db =mydb 

server-id =1 

log-bin =mysql-bin】

Configuring theMaster:
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):
thisis a comment --> #skip-networking
shouldlook 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 themaster, which log file it
should use, and we have to specify that this MySQL server is themaster. 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

 RestartMySQL daemon:
]# service mysqld restart

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

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

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

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

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

Nowthat we’re logged in as the root user we need to create a user withdatabase 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;
Whichshould 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 setuplater.)
Also, DO NOT logout of the MySQL shell after "FLUSH TABLES WITHREAD LOCK;" or you will
lose the lock.

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

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

Thelatter has the disadvantage the the database on the master willbe locked during this 
operation, so if you have a large database on a high-trafficproduction system, this is not
what you want, and recommend to follow the first method in thiscase. 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 itbefore
executing your command.

Thiswill 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 MySQLcommands:
mysql> UNLOCK TABLES;
mysql> FLUSH PRIVILEGES;
mysql> exit;

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

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

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

 Ifyou have made an SQL dump of mydb onthe master and have transferred it to the slave, then it is nowtime to import the SQL dump into our newly createdmydb onthe 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 itbefore
executing your command.

 Nextwe have to tell MySQL on the slave that it is the slave, that themaster is 192.168.0.6,and that the master database to watchis mydb.Therefore we add the following linesto /etc/my.cnf:
Add these lines in /etc/my.cnf on the slaveserver:【和主数据库一样，配置文件里面加这几行，这里没有问题，直接这样加到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
Restartthe MySQL server on the slave:
]# service mysqld restart【重启生效】

 Ifyou have not imported the master mydb byusing an SQL dump, but want to gothe LOADDATA FROM MASTER; way,then it is now time to get the data from themaster mydb::
login to the MySQL shell:
]$ mysql -u root -p
(note:this command assumes you are not root on the system, but shouldwork even if you are.)

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

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

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

(note:make sure to change the example values hereappropriately.)
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 thisexample it is 192.168.0.6).
* MASTER_USER is the user we granted replication privileges on themaster.
* MASTER_PASSWORD is the password of MASTER_USER on themaster.
* MASTER_LOG_FILE is the file MySQL gave back when you ran SHOWMASTER STATUS; on the master.
* MASTER_LOG_POS is the position MySQL gave back when you ran SHOWMASTER STATUS; on the master.

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

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

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