简单的mysql主从同步

简单的mysql主从同步

 
一、环境：

1、主从服务器上的mysql版本要一样或者主上的版本比从的高

2、要同步的数据库名为tongbu

3、master IP：192.168.1.179

4、slave IP：192.168.1.178

二、Mysql主服务器配置

1、编辑mysql配置文件

#vi /etc/my.cnf

根据情况加入如下行

server-id = 1

log-bin =tongbu-bin  #日志文件名

binlog-do-db = databasename  #需要备份的数据库名

binlog-ignore-db = databasename  #不需要备份的数据库名

log-slave-updates  #这个一定要加上，否则不会给更新的记录写到二进制文件里

#slave-skip-errors  #跳过错误，继续执行复制操作，一般是关闭的。

3、  添加用户

Mysql>grant relication slave on *.* to ‘tongbu’@’192.168.1.%’ identified by ‘123456’;

在slave上测试下能否连接上：mysql –h 192.168.1.178 –u tongbu –p

4、  重启mysql

# /etc/init.d/mysqld restart

5、  登录mysql查看信息

mysql> show master status;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| tongbu-bin.000001  |     98 | tongbu       |                  |

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

1 row in set (0.00 sec)

红色的是重点！！！在从服务器上要用到。

三、在mysql从服务器上配置

1、编辑mysql

vi /etc/my.cnf

添加如下行

server-id = 2

log-bin = tongbu-bin   #跟主的要一致

2、在从上设置同步

mysql> stop slave；

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.179',MASTER_USER='tongbu',MASTER_PASSWORD='123456',MASTER_LOG_FILE='tongbu-bin.000001',MASTER_LOG_POS=863;

mysql> start slave；

mysql> show slave status\G

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

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.1.179

                Master_User: tongbu

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: tongbu-bin.000001

        Read_Master_Log_Pos: 863

             Relay_Log_File: mysqld-relay-bin.000002

              Relay_Log_Pos: 236

      Relay_Master_Log_File: tongbu-bin.000001

           Slave_IO_Running: Yes

          Slave_SQL_Running: 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: 863

            Relay_Log_Space: 236

            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

1 row in set (0.00 sec)

红色的都是yes说明成功了。

三、查看同步情况

1、在主服务器上创建数据库tongbu

mysql> create database tongbu;

mysql> use tongbu；

mysql> create table tongbu(id int,name varchar(50));

mysql> insert into tongbu values(1,'zhang');

mysql> insert into tongbu values(2,'zhao');

2、在从服务器上查看

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| snort              |

| snort_archive      |

| test               |

| tongbu             |

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

6 rows in set (0.00 sec)

 

mysql>use tongbu

mysql> show tables;

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

| Tables_in_tongbu |

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

| tongbu           |

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

1 row in set (0.00 sec)

 

mysql> select * from tongbu;

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

| id   | name  |

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

|    1 | zhang |

|    2 | zhao  |

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

2 rows in set (0.00 sec)

至此大功告成了。