本文Raksmart主要就如何在独立服务器上部署MySQL做详细介绍,内容及步骤仅供参考。

一、保证Master库和Slave库的数据和结构一致

可以通过mysqldump或者XtraBackup工具对Master库进行全备,再把备份文件传到Slave库,Slave库利用改备份进行恢复,使得两者一致。

我这里的Slave库是直接复制Master库生成的,所以不需要做这一步了。

二、配置两个库的参数文件

Master库需要打开二进制日志

Master库需要设置与Slave库不同的server-id

点击(此处)折叠或打开

[root@potato data]vi /etc/my.cnf

binlog_format = mixed

server-id = 203306

log-bin = mybinlog

Slave库只需配置server-id即可

点击(此处)折叠或打开

[root@tomato data]vi /etc/my.cnf

server-id = 203307

三、创建用来进行复制的用户并且赋权

必须给予用户replication slave权限,指定Slave库的主机地址

点击(此处)折叠或打开

root@localhost:mysql.sock 05:02:32 [(none)]》grant replication slave on *.* to repl@‘192.168.161.128’ identified by ‘repl’;

四、让Slave库change到Master库

查看Master库此时的日志位置

点击(此处)折叠或打开

root@localhost:mysql.sock 05:02:32 [(none)]》show master status ;

+—————–+———-+————–+——————+——————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+—————–+———-+————–+——————+——————-+

| mybinlog.000004 | 331 | | | |

+—————–+———-+————–+——————+——————-+

1 row in set (0.00 sec)

从库开始change到master库上

点击(此处)折叠或打开

root@localhost:mysql.sock 04:29:15 [(none)]》change master to

-》 master_host=‘192.168.161.128’,

-》 master_port=3306,

-》 master_user=‘repl’,

-》 master_password=‘repl’,

-》 master_log_file=‘mybinlog.000004’,

-》 master_log_pos=331;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect.。.

Connection id: 2

Current database: *** NONE ***

Query OK, 0 rows affected, 2 warnings (0.17 sec)

五、Slave库执行开始复制命令

点击(此处)折叠或打开

root@localhost:mysql.sock 04:40:11 [(none)]》start slave;

到此,主从复制就已经配置完毕了

六、对搭建的环境进行测试测试

主库开始插入数据测试

点击(此处)折叠或打开

root@localhost:mysql.sock 04:40:11 [(none)]》use lala;

root@localhost:mysql.sock 04:51:23 [(lala)]》create table haha(id int);

root@localhost:mysql.sock 04:51:23 [(lala)]》insert into haha values(1);

从库是否接受到数据

点击(此处)折叠或打开

root@localhost:mysql.sock 04:52:55 [(none)]》select * from lala.haha;

+——+

| id |

+——+

| 1 |

+——+

1 row in set (0.00 sec)

查看Slave状态

点击(此处)折叠或打开

root@localhost:mysql.sock 04:57:23 [(none)]》show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.161.128

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mybinlog.000004

Read_Master_Log_Pos: 620

Relay_Log_File: mysql-relay-bin.000003

Relay_Log_Pos: 571

Relay_Master_Log_File: mybinlog.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: 620

Relay_Log_Space: 744

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: 203306

Master_UUID: af3609cd-b426-11e6-a997-000c29d55626

Master_Info_File: /data/mysql/mytest_3306/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

主从复制部署测试成功,推荐相关内容,如何运行与关闭MySQL服务器