mysql主从数据库配置详解

1.准备工作

1.1     master（192.168.142.128）  slave（192.168.142.129）

1.2     master 服务器与slave服务器之间应该能够ping通，并开启了3306（mysql默认端口）端口

1.3     master服务器与salve服务器安装mysql   (yum install mysql mysql-server)

1.4     master数据库与slave数据库版本要保持一致

1.5     master数据库与slave数据库分别设置远程访问权限(GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;)

2.修改master主数据库配置

2.1   修改master（192.168.142.128）配置文件

        # vi /etc/my.cnf

   [mysqld]

   log-bin=mysql-bin  //开启binlog日志功能(必须)

  server-id=128        //服务器唯一ID，默认为1，一般取IP最后一位

3.修改slave主数据库配置

3.1   修改slave（192.168.142.129）配置文件

        # vi /etc/my.cnf

   [mysqld]

   log-bin=mysql-bin  //开启binlog日志功能(必须)

  server-id=129  //服务器唯一ID，默认为1，一般取IP最后一位

4.重启master和slave服务器的mysql

4.1   # /etc/init.d/mysqld restart

5.master主服务器授权slave

5.1   # mysql -uroot -proot     //登录master主服务器mysql

        mysql > GRANT REPLICATION SLAVE ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword';

        注：@后面的%代表可以让所有客户端连接，可授权单独的ip（192.168.142.129）连接

6.查询master主服务器mysql状态

6.1   # mysql -uroot -proot     //登录master主服务器mysql

         mysql> show master status;          +----------------------------+---------------+------------------------+----------------------------+

          | File                                | Position     | Binlog_Do_DB     | Binlog_Ignore_DB    |

         +----------------------------+---------------+------------------------+----------------------------+

          | mysql-bin.000006      |      378        |                                 |                                       |

         +----------------------------+---------------+------------------------+----------------------------+

         注：执行完此操作后不再对master主服务器mysql进行操作，防止master主服务器masql状态值变化

7.配置slave从服务器mysql

7.1   # mysql -uroot -proot     //登录slave主服务器mysql

         mysql> CHANGE MASTER TO MASTER_HOST='192.168.142.128',

           -> MASTER_USER='myuser',

           -> MASTER_PASSWORD='mypassword',

           -> MASTER_LOG_FILE='mysql-bin.000006',

           -> MASTER_LOG_POS=378;

        注：MASTER_HOST                     : master主服务器ip

                MASTER_USER                     : master主服务器用户名

                MASTER_PASSWORD         : master主服务器密码

                MASTER_LOG_FILE             : master主服务器二进制日志文件

                MASTER_LOG_POS             : master主服务器二进制日志文件开始位置

7.2    mysql> start slave;                      //启动slave从服务器复制功能

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

8.1   # mysql -uroot -proot     //登录slave主服务器mysql

        mysql> show slave status\G;

        *************************** 1. row ***************************

                  Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.142.128                                                                 //master主服务器地址

                  Master_User: root                                                                                         //master主服务器授权账户

                  Master_Port: 3306                                                                                        //master主服务器mysql数据库端口

                  Connect_Retry: 60

                  Master_Log_File: mysql-bin.000006                                                       //二进制日志文件

                  Read_Master_Log_Pos: 378                                                                    //同步读取二进制日志的位置

                  Relay_Log_File: mysqld-relay-bin.000002

                  Relay_Log_Pos: 524

                  Relay_Master_Log_File: mysql-bin.000006

                  Slave_IO_Running: Yes                                                                           //此状态必须为YES

                  Slave_SQL_Running: Yes                                                                       //此状态必须为YES

                  Replicate_Do_DB:

                  Replicate_Ignore_DB:

                  Replicate_Do_Table:

                  Replicate_Ignore_Table:

                  Replicate_Wild_Do_Table:

                  Replicate_Wild_Ignore_Table:

                  Last_Errno: 0

                  Last_Error:

                  Skip_Counter: 0

                  Exec_Master_Log_Pos: 378

                  Relay_Log_Space: 681

                  Until_Condition: None

                  Until_Log_File:

                  Until_Log_Pos: 0

                  Master_SSL_Allowed: No

                  Master_SSL_CA_File:

                  Master_SSL_CA_Path:

                  Master_SSL_Cert:

                  Master_SSL_Cipher:

                  Master_SSL_Key:

                  Seconds_Behind_Master: 0

                  Master_SSL_Verify_Server_Cert: No

                  Last_IO_Errno: 0

                  Last_IO_Error:

                  Last_SQL_Errno: 0

                  Last_SQL_Error:

                  Replicate_Ignore_Server_Ids:

                  Master_Server_Id: 128

注：至此主从服务器配置完成

9.主从mysql服务器测试

9.1   # mysql -uroot -proot                                                //登录master主服务器mysql

         mysql> show databases;                                        //先查看一下数据库

         +--------------------------------+

          | Database                         |

         +--------------------------------+

          | information_schema      |

          | mysql                                |

          | performance_schema   |

         +--------------------------------+

        #创建一个test数据库

        mysql> create database test;                                

        Query OK, 1 row affected (0.01 sec) 

        #选择数据库

        mysql> use test;                                                                                                          

        Database changed         

        #创建表

        mysql> CREATE TABLE `user` (                                                                             

                ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

                ->   `name` varchar(20) NOT NULL DEFAULT '',

                ->   `age` int(10) unsigned NOT NULL,

                ->   PRIMARY KEY (`id`)

                -> ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

       #插入数据

       mysql> INSERT INTO `user` (`id`,`name`,`age`) VALUES (1,'zhangsan',10);

       Query OK, 1 row affected (0.00 sec)

9.2  验证奇迹（看看slave数据库是否已经创建了test数据库和user表并写入数据了）

       # mysql -uroot -proot     //登录slave主服务器mysql

       #先查看一下数据库

       mysql> show databases;                                       

       +--------------------------------+

        | Database                         |

       +--------------------------------+

        | information_schema      |

        | mysql                                |

        | performance_schema   |

        | test                                    |

       +--------------------------------+ 

        #选择数据库

        mysql> use test;                                                                                                          

        Database changed 

       #查看表

       mysql> show tables;

      +-------------------------+

       | Tables_in_test       |

      +-------------------------+

       | user                         |

      +-------------------------+

      #查看数据

      mysql> select * from user;

      +-------+----------------+--------+

       | id      | name           | age    |

      +-------+----------------+--------+

       |  1      | zhangsan    |  10     |

      +-------+----------------+--------+

       至此所有测试完成