mysql主从服务的配置（一主一从结构）

最新推荐文章于 2023-12-13 18:09:58 发布

运维螺丝钉

最新推荐文章于 2023-12-13 18:09:58 发布

阅读量340

点赞数

分类专栏： MySQL 文章标签： MySQL 一主一从结构

本文链接：https://blog.csdn.net/weixin_42816196/article/details/82598934

版权

MySQL 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

什么是主从同步？

对指定库进行异地同步？

例如：对游戏网站的注册帐号进行备份，通过计划任务进行备份；

现在通过另外一台服务器进行备份数据——这就是主从同步备份

主服务器被客户端访问，从服务备份数据，实现数据的自动备份

主从同步也可以做，数据分发，减少主服务器的访问压力，（适用与大并发量的数据库）

至少有两台服务器

搭建主从服务：

生产环境，是把主服务器完全备份，拷贝给从服务器

mysql> select @@hostname;

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

| @@hostname |

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

| host52 |

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

1 row in set (0.00 se

1.配置步骤：

主master库配置如下：

启动binlog日志文件

[root@host51 ~]# vim /etc/my.cnf

server_id=51

log-bin=master51

binlog_format="mixed"

:wq

[root@host51 ~]# systemctl restart mysqld

验证是否启动binlog日志

[root@host51 ~]# ls /var/lib/mysql/master51*

/var/lib/mysql/master51.000001 /var/lib/mysql/master51.index

用户授权：给从库添加连接时使用的用户名

mysql> grant replication slave on *.* to repluser@"192.168.4.52" identified by "123qqq...A";

Query OK, 0 rows affected, 1 warning (0.04 sec)

replication slave ——给从服务器授予一个同步的权限

查看正在使用的binlog日志信息

mysql> show master status;

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

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

| master51.000001 | 452 | | | |

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

1 row in set (0.00 sec)

记录文件是master51.000001

再进行记录就是从452开始记录

从库slave库配置如下：

mysql> show slave status;——查看是否从库

Empty set (0.00 sec)

mysql> show master status;——查看binlog日志

Empty set (0.00 sec)

指定server_id

[root@host52 ~]# vim /etc/my.cnf

[mysqld] ——只需指定server_id就可以，

server_id=52

:wq

[root@host52 ~]# systemctl restart mysqld

不需要启动binlog日志

测试授权用户

[root@host52 ~]# mysql -h192.168.4.51 -urepluser -p123qqq...A

mysql> show grants;——查看权限列表

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

| Grants for repluser@192.168.4.52 |

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

| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.4.52' |

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

1 row in set (0.00 sec)

mysql> show databases;——只能展示默认系统库

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

| Database |

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

| information_schema |

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

1 row in set (0.01 sec)

mysql> quit

Bye

管理本机登陆指定主库信息

mysql> show slave status; ——查看是否从库

Empty set (0.00 sec)

mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123qqq...A",master_log_file="master51.000002",master_log_pos=154;

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

查看配置

mysql> start slave;——启动slave进程

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G;——查看从服务器的配置

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.4.51

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master51.000002

Read_Master_Log_Pos: 154

Relay_Log_File: host52-relay-bin.000002

Relay_Log_Pos: 319

Relay_Master_Log_File: master51.000002

Slave_IO_Running: Yes IO线程必须处于yes状态

Slave_SQL_Running: Yes SQL线程必须处于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: 154

Relay_Log_Space: 527

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

Master_UUID: 7cd7a05c-89b1-11e8-a8a0-52540024ea31

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

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

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

客户端测试主从同步配置？

主服务器：增，删，改，查操作

mysql> create datadase gamedb;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datadase gamedb' at line 1

mysql> create database gamedb;

Query OK, 1 row affected (0.06 sec)

mysql> create table gamedb.t1;

ERROR 1113 (42000): A table must have at least 1 column

mysql> create table gamedb.t1(id int);

Query OK, 0 rows affected (0.24 sec)

给其他服务器授权：

mysql> grant select,insert on gamedb.* to webadmin@"%" identified by "123qqq...A";

Query OK, 0 rows affected, 1 warning (0.03 sec)

这些建表建库授权的操作，记录在binlog日志里

客户端连接主库存储数据

50:操作

[root@host50 ~]# mysql -h192.168.4.52 -uwebadmin -p123qqq...A

mysql> show grants;

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

| Grants for webadmin@% |

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

| GRANT USAGE ON *.* TO 'webadmin'@'%' |

| GRANT SELECT, INSERT ON `gamedb`.* TO 'webadmin'@'%' |

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

2 rows in set (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| gamedb |

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

2 rows in set (0.00 sec)

mysql> insert into t1 valu(8888);

mysql> insert into gamedb.t1 values(8888);