mysql 主从配置

          数据库查询比较慢，领导提议做个mysql主从配置，一主两备，结果刚开始做又说不用了，本着学习的态度自己还是操作了一下，留个笔记。
首先，安装这3台mysql的机器是公司openstack的虚拟机中，有一些不稳定的情况存在，导致虚拟机删了建，建了删。

1.安装3个mysql，版本一致。
    由于之前安装过，所以打开文章按照步骤操作一下，比较省事，参考文章：http://blog.csdn.net/huoyunshen88/article/details/18702405

2.修改主服务器master:

[root@yg-rhel-test2 ~]# vi /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin      //[必须]启用二进制日志
    server-id=26         //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3.修改从服务器slave 2台，不同的是id:    

[root@yg-rhel-test1 ~]# vi /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin      //[必须]启用二进制日志
    server-id=24         //[必须]服务器唯一ID，默认是1，一般取IP最后一段

   
4.重启两台服务器的mysql
   /etc/init.d/mysql restart或者
   service mysql restart

5.在主服务器上授权给slave，并查询master的状态信息:

[root@yg-rhel-test2 ~]# mysql -u root -proot
mysql> GRANT REPLICATION slave ON *.* to 'root'@'172.22.14.%' identified by 'root';  //授权账号root@某个机器上，授权密码是root。
mysql> show master status;              //查询master的状态信息，为配置slave使用
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      478 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)     

6.配置从服务器Slave：

[root@yg-rhel-test1 ~]# mysql -u root -proot
mysql> CHANGE MASTER to MASTER_HOST='172.22.14.26',MASTER_USER='root',MASTER_PASSWORD='root',
    ->          MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=320;
mysql> start slave;
   

8.检查从服务器复制功能状态：

mysql> show slave status;
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Slave_IO_State       | Master_Host  | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                 | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                                     | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Connecting to master | 172.22.14.26 | root        |        3306 |            60 | mysql-bin.000001 |                 320 | yg-rhel-test1-relay-bin.000001 |             4 | mysql-bin.000001      | Connecting       | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 320 |             120 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |             | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for the slave I/O thread to update it |              86400 |             |                         |                          |                |                    |                    |                   |             0 |
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
1 row in set (0.00 sec)

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
以上操作过程，主从服务器配置完成。
        
遇到的问题，以及解决的命令：
1.查看MySQL端口号  inux 下 通过netstat -a 来查看，或者mysql> show variables like 'port';

2.查看Mysql服务运行状态 ：ps -ef|grep mysqld

3.MySQL错误：Can't connect to MySQL server (10060)
从以下几个方面入手，找出错误的原因：
1.网络不通，经查询ok。
检查能不能ping通。

2.防火墙设置。防火墙是否放过mysql的进程，是否屏蔽了mysql的3306端口。
防火墙关闭了，没有问题，

3.mysql的账户设置，

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;     //任何远程主机都可以访问数据库  
mysql> FLUSH PRIVILEGES;    //需要输入次命令使修改生效  

结果还是连不到，现在只有怀疑是openstack的问题了，后来发现，openstack有自己的访问规则，相等于一层防火墙，把所有端口放开后，就可以本地连接mysql了。

参考文章：http://369369.blog.51cto.com/319630/790921/
          http://blog.csdn.net/ylqmf/article/details/6860458