|NO.Z.00096|——————————|^^ 部署 ^^|——|Linux&MySQL集群.V04|——|多主一从|
一、MySQL 多主一从
### --- MySQL 多主一从
~~~ 主服务器配置
~~~ 开启二进制日志，启动服务### --- 部署规划
~~~ centos6.x server21:20.20.20.21 主服务器1
~~~ centos6.x server22:20.20.20.22 主服务器2
~~~ centos6.x server23:20.20.20.23 从服务器1二、配置主服务器1
### --- 配置主服务器1
[root@server21 ~]# yum install -y mysql mysql-server
[root@server21 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=21
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid[root@server21 ~]# chkconfig mysqld on
[root@server21 ~]# service mysqld start### --- 授权
[root@server21 ~]# mysql
mysql> grant replication slave on *.* to slave@'20.20.20.23' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 257 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)三、配置主服务器2
### --- 配置主服务器2
[root@server22 ~]# yum install -y mysql mysql-server
[root@server22 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=22
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid[root@server22 ~]# chkconfig mysqld on
[root@server22 ~]# service mysqld start### --- 授权
[root@server22 ~]# mysql
mysql> grant replication slave on *.* to slave@'20.20.20.23' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 257 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)四、配置从服务器1
### --- 配置从服务器1
[root@server23 ~]# yum install -y mysql mysql-server
[root@server23 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log
[mysqld21]
port=3306
datadir=/var/lib/mysqla/
pid-file=/var/lib/mysqla/mysqld.pid
socket=/var/lib/mysqla/mysql.sock
user=mysql
server-id=23 # 当前服务器的ID
[mysqld22]
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=23 # 当前服务器的ID### --- 初始化数据库，生成目录mysqla,mysqlb
[root@server23 ~]# mysql_install_db --datadir=/var/lib/mysqla --user=mysql
[root@server23 ~]# mysql_install_db --datadir=/var/lib/mysqlb --user=mysql
[root@server23 ~]# ll /var/lib/mysql
mysqla/ mysqlb/### --- 设置 mysqla,mysqlb 目录及以下文件的属主为 mysql(防止出现权限问题)
[root@server23 ~]# chown -R mysql /var/lib/mysqla/
[root@server23 ~]# chown -R mysql /var/lib/mysqlb/### --- 启动从服务器线程
[root@server23 ~]# mysqld_multi --defaults-file=/etc/my.cnf start 21
[root@server23 ~]# mysqld_multi --defaults-file=/etc/my.cnf start 22
[root@server23 ~]# netstat -antp
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1864/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 1986/mysqld ### --- 分别登录并保存授权信息
~~~ 登录3306端口的并保存授权信息(20.20.20.21)
[root@server23 ~]# mysql -uroot -P 3306 -S /var/lib/mysqla/mysql.sock
mysql> change master to
-> master_user='slave',
-> master_password='123456',
-> master_host='20.20.20.21',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=257;
Query OK, 0 rows affected (0.05 sec)~~~ 启动从的进程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)~~~ 登录3307端口的并保存授权信息(20.20.20.22)
[root@server23 ~]# mysql -uroot -P 3307 -S /var/lib/mysqlb/mysql.sock
mysql> change master to
-> master_user='slave',
-> master_password='123456',
-> master_host='20.20.20.22',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=257;
Query OK, 0 rows affected (0.08 sec)~~~ 启动从的进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)五、验证测试
### --- 在主服务器1：20.20.20.21上创建数据库，看从服务器20.20.20.23是否同步数据库
~~~ 主服务器1上创建abc
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)~~~ 从服务器上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)~~~ 主服务器1上删除abc
mysql> drop database abc;
Query OK, 0 rows affected (0.00 sec)~~~ 从服务器上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)### --- 在主服务器2：20.20.20.22上创建数据库，看从服务器20.20.20.23是否同步数据库
~~~ 主服务器2上创建a111；
mysql> create database a111;
Query OK, 1 row affected (0.00 sec)~~~ 从服务器上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a111 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)~~~ 主服务器2上删除a111；
mysql> drop database a111;
Query OK, 0 rows affected (0.00 sec)### --- 两个各自接受各自的授权。
~~~ 从服务器上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor
分类:
cdv007-network
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」