一步一步mysql（高可用复制）

来源：互联网发布：知乎我爱男保姆编辑：程序博客网时间：2024/05/20 10:53

环境：centos

外网内网

ip：node1：mysql-master 192.168.13.128 10.0.0.129

node2：mysql-slave 192.168.13.129 10.0.0.128

node1和node2分别安装mysql5.5

Node1配置：

vi /etc/my.cnf

[mysqld]

log-bin=master-bin
log-bin-index= master-bin.index

server-id =1

Node2配置：

mysqld]

lrelay-log-index = slave-relay-bin.index
relay-log = slave=relay-bin

server-id =2

node1:创建用户并授权复制；

mysql> create user repl_user;
Query OK, 0 rows affected (0.08 sec)
mysql> grant replication slave on *.* to repl_user identified by 'xyzzy';

以上步骤基恩完成了最基本的配置，最后就需要在slave上镜slave指向master

node2：

mysql> change master to master_host = '10.0.0.129', master_port =3306, master_user = 'repl_user', master_password = 'xyzzy';

测试效果：

node1：

mysql> use test;
Database changed
mysql> create table tbl(test TEXT);
Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO tbl values("hello");
Query OK, 1 row affected (0.13 sec)

mysql> select * from tbl;
+-------+
| test |
+-------+
| hello |
+-------+
1 row in set (0.06 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)

mysql> show binlog events\g
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| master-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.27-log, Binlog ver: 4 |
| master-bin.000001 | 107 | Query | 1 | 187 | create user repl_user |
| master-bin.000001 | 187 | Query | 1 | 277 | use `test`; create table tbl(test TEXT) |
| master-bin.000001 | 277 | Query | 1 | 345 | BEGIN |
| master-bin.000001 | 345 | Query | 1 | 439 | use `test`; INSERT INTO tbl values("hello") |
| master-bin.000001 | 439 | Xid | 1 | 466 | COMMIT /* xid=12 */ |
| master-bin.000001 | 466 | Rotate | 1 | 510 | master-bin.000002;pos=4 |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------+
7 rows in set (0.10 sec)

mysql>

node1创建数据库测试：

Node2启动start slave

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.129
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: slave=relay-bin.000011
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000004
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: 107
Relay_Log_Space: 454
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: 1
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
+--------------------+

简单吧就这样一个简单mysql复制成功