Linux 下实现Mysql主从同步


一、实验环境准备:

主库(Master):CentOS release 5.9  x86_64

IP:10.45.172.40

mysql Ver 14.12 Distrib 5.0.95

从库CentOS release 5.9  x86_64

IP:10.45.172.37

mysql Ver 14.12 Distrib 5.0.95,

测试是否安装mysql:service mysql restart  或servicemysqld restart

 

安装mysql安装:

在官方网站下载以下安装包 http://dev.mysql.com/downloads/mysql#downloads

rpm -ivh MySQL-server-5.5.25a-1.rhel5.x86_64  MySQL-client-5.5.25a-1.rhel5.x86_64 MySQL-devel-5.5.25a-1.rhel5.x86_64

设置修改密码 /usr/bin/mysqladmin -u root password‘rootadmin’

 

 

二、主库的操作

1.查看主库服务器/usr/share/mysql目录下的cnf文件

#ll /usr/share/mysql/*.cnf

 

其中.cnf文档如下:

my-small.cnf    内存少于或等于64M,只提供很少的的数据库服务

my-medium.cnf   内存在32M--64M之间而且和其他服务一起使用,例如web

my-large.cnf    内存有512M主要提供数据库服务

my-huge.cnf     内存有1G到2G,主要提供数据库服务

my-innodb-heavy-4G.cnf 内存有4G,主要提供较大负载数据库服务(一般服务器都使用这个)

 

2.复制文件到/etc下并更名为my.cnf

#cp /usr/share/mysql/my-innodb-heavy-4G.cnf/etc/my.cnf

 

3.修改my.cnf

#vi /etc/my.cnf  修改以下参数:

server-id = 1

log-bin=mysql-bin

binlog-do-db=test#需要同步的数据库,如果同步多个库,需要配置多行

配置完重启:

service mysql restart

 

4.建立同步用的数据库账户

mysql> grant replication slave on *.* to‘testuser‘@‘10.45.172.37‘ identified by ‘test123‘;

Query OK, 0 rows affected (0.00 sec)

 

5.锁住主库表,停止数据更新。

mysql> flush tables with read lock;

 

6.显示主库信息并记录

mysql> show master status;

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

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |

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

| mysql-bin.000001 |      566 |              |                  |

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

1 row in set (0.00 sec)

 

将上面的日志名mysql-bin.000001和位置566记录下来,从库配置时要用到

 

7.备份所有库文件并复制到从库服务器上

在从库上执行:

cd /var/lib/mysql

mkdir /tmp/bak

 mv */tmp/bak

备份后,从主库复制过来:

scp -r [email protected]:/var/lib/mysql/*./

8.数据库复制完成后对主库所有表解锁

#unlock tables;

三、从库的操作

1.修改配置文件:

#vi /etc/my.cnf

server-id = 2 将这个ID号改为2

log-bin=mysql-bin 此项如果开启建议关闭

master-host = 10.45.172.40 配置主库的IP

master-user = testuser 同步用的账户

master-password = test123 同步用的账户密码

master-port = 3306 同步数据库的端口号

2.调整权限

#chown -R mysql:mysql /var/lib/mysql

 

3.重启mysql服务

#service mysql restart

 

4.手动同步

mysql>slave stop

mysql> CHANGE MASTER TO

MASTER_HOST=‘10.45.172.40‘,

MASTER_USER=‘testuser‘,

MASTER_PASSWORD=‘test123‘,

MASTER_PORT=3306,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=471,

MASTER_CONNECT_RETRY=60;

5.启动从库

mysql>slave start;

6.检查从库是否正常同步

mysql> show slave status \G;

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

             Slave_IO_State: Waiting for master to sendevent

               Master_Host: 10.45.172.40

               Master_User: testuser

               Master_Port: 3306

             Connect_Retry: 60

           Master_Log_File: mysql-bin.000001

        Read_Master_Log_Pos:471

            Relay_Log_File: mysqld-relay-bin.000002

             Relay_Log_Pos: 235

     Relay_Master_Log_File: mysql-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:471

           Relay_Log_Space: 235

           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)

 

四、测试

1.主库执行

mysql> use test

Database changed

mysql> create table tt(id int,namevarchar(20));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into tt values(1,‘jhp‘);

Query OK, 1 row affected (0.00 sec)

 

2.在从库中检查是否同步过来:

mysql> select * from test.tt;

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

| id  | name |

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

|   1 | jhp  |

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

1 row in set (0.00 sec)

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。