Mysql5.1.71主从复制replication配置

 Mysql主从复制

主库：192.168.114.11

备库：192.168.114.12

(1)首先确保主从服务器上的Mysql版本相同

(2)在主服务器上,设置一个从数据库的账户,使用REPLICATIONSLAVE赋予权限

1.主从安装mysql

安装

[root@rhel65-mysql01 ~]# yum install mysqlmysql-server

启动

[root@rhel65-mysql01 ~]# /etc/init.d/mysqldstart
[root@rhel65-mysql01 ~]# mysql
mysql>select version();
+-----------+
|version() |
+-----------+
|5.1.71    |
+-----------+
1row in set (0.00 sec)
 
mysql>show databases;
+--------------------+
|Database           |
+--------------------+
|information_schema |
|mysql              |
|test               |
+--------------------+
3rows in set (0.00 sec)

删除匿名用户

mysql>use mysql
Databasechanged
mysql>delete from user where user='';
QueryOK, 2 rows affected (0.00 sec)

2.master节点设置

修改my.cnf

[root@rhel65-mysql01~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=11
binlog_ignore_db=mysql

说明：

server-id=11中的11可以任定义，但要唯一。

 

建立帐户并授权salve

[root@rhel65-mysql01~]# mysql -u root -ptan
mysql>create user 'standby'@'192.168.114.12' identified by '123456';
mysql>grant replication salve on *.* to 'standby'@'192.168.114.12' identified by'123456';
 

说明： IP192.168.114.12为从节点IP。

锁定数据库以防止写入数据

mysql>flush tables with read lock;

导出数据库并传送到备库

[root@rhel65-mysql01~]# mysqldump -u root -ptan --all-database --lock-tables=false -->/tmp/all.sql
[root@rhel65-mysql01~]# scp /tmp/all.sql root@192.168.114.12:/tmp

重启服务

[root@rhel65-mysql01~]# /etc/init.d/mysqld restart

查看binlog

mysql>show master status;
+------------------+----------+--------------+------------------+
|File             | Position |Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|mysql-bin.000002 |      384 |              | mysql            |
+------------------+----------+--------------+------------------+
1row in set (0.00 sec)

解锁数据表：

 

mysql>unlock tables;

QueryOK, 0 rows affected (0.00 sec)

3.slaver节点设置

导入主库数据

[root@rhel65-mysql02~]# mysql -u root -ptan < /tmp/all.sql

 配置my.cnf

[root@rhel65-mysql02~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=12

重启mysql服务

 [root@rhel65-mysql02 ~]# /etc/init.d/mysqldrestart

配置从服务

mysql>change master to
master_host='192.168.114.11',master_user='standby',
master_password='123456',master_port=3306,
master_log_file='mysql-bin.000002',master_log_pos=384,
master_connect_retry=10;

说明：

master_host是主从服务器IP

master_log_file和master_log_pos是前面master用show master status查出来的。

启动slave进程

mysql>start slave;

查看复制状态

mysql>show slave status\G
***************************1. row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 192.168.114.11
                  Master_User: standby
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
            Read_Master_Log_Pos: 384
               Relay_Log_File:mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
           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: 384
              Relay_Log_Space: 407
              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:
1row in set (0.00 sec)

注意：       

Read_Master_Log_Pos: 384

             Slave_IO_Running:Yes

            Slave_SQL_Running:Yes

查看日志：

[root@rhel65-mysql02~]# more /var/log/mysqld.log

4.简单测试

主库操作：

mysql>create database tan;
QueryOK, 1 row affected (0.00 sec)
mysql>use tan;
Databasechanged
mysql>create table t1(id int(10),name varchar(50));
QueryOK, 0 rows affected (0.01 sec)
mysql>insert into t1 values(1,'qing');
QueryOK, 1 row affected (0.00 sec)

备库查看：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.11
                  Master_User: standby
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 664
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mysql-bin.000002
             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: 664
              Relay_Log_Space: 687
              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: 
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| tan                |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use tan
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_tan |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | qing |
+------+------+
1 row in set (0.00 sec)