安装前准备与要求 |
您所在的位置：网站首页 › mysql80导入sql文件 › 安装前准备与要求 |
|
部署 MySQL 主从集群相关信息在 Docker 中部署 MySQL 主从集群主从数据库配置文件Disabling symbolic-links is recommended to prevent assorted security risks服务端默认utf8编码默认存储引擎主从配置Compatible with versions before 8.0设置客户端编码设置mysql客户端默认编码Custom config should go hereCustom config should go here进入数据库创建用户授权进入数据库主从配置开启主从同步再查看主从同步状态获取密码登录数据库修改密码开启远程访问默认存储引擎# Compatible with versions before 8.0主从配置设置客户端编码设置mysql客户端默认编码修改为utf8编码默认存储引擎Compatible with versions before 8.0主从配置设置客户端编码设置mysql客户端默认编码启动从节点再查看主从同步状态部署 MySQL 主从集群 此文档描述在 Docker 和 Centos 7 中搭建 MySQL主从集群的过程；该方案在主数据库出现宕机时并不会自行切换至备数据库，备数据库仅提供备份功能；欢迎参与 Rainbond开源社区 提供数据库高可用自动切换方案。 相关信息数据库类型版本字符编码MySQL8.0utf8mb4 在 Docker 中部署 MySQL 主从集群在主从服务器中执行以下命令获取镜像： docker pull mysql:8.0主从数据库配置文件主数据库配置文件 vim /var/lib/mysql/my.cnf [mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULL# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# 服务端默认utf8编码character-set-server=utf8mb4# 默认存储引擎default-storage-engine=INNODB# 主从配置log-bin=binlogserver-id=121gtid-mode=onenforce-gtid-consistency=onlog-slave-updates=onexpire_logs_days=14# Compatible with versions before 8.0default_authentication_plugin=mysql_native_passwordskip-host-cacheskip-name-resolve[client]#设置客户端编码default-character-set=utf8mb4[mysql]# 设置mysql客户端默认编码default-character-set=utf8mb4# Custom config should go here!includedir /etc/mysql/conf.d/# Custom config should go here!includedir /etc/mysql/conf.d/``` 从数据库配置文件vim /var/lib/mysql/my.cnf[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 服务端默认utf8编码character-set-server=utf8mb4 默认存储引擎default-storage-engine=INNODB 主从配置server-id=122 gtid-mode=on enforce-gtid-consistency=on log-slave-updates=on expire_logs_days=14 Compatible with versions before 8.0default_authentication_plugin=mysql_native_password skip-host-cache skip-name-resolve [client] 设置客户端编码default-character-set=utf8mb4 [mysql] 设置mysql客户端默认编码default-character-set=utf8mb4 Custom config should go here!includedir /etc/mysql/conf.d/ Custom config should go here!includedir /etc/mysql/conf.d/ ### 启动主数据库docker run —name mysql_master —restart=always \ -p 3306:3306 \ -v /var/lib/mysql/my.cnf:/etc/mysql/my.cnf \ -v /var/lib/mysql/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=Root123456 \ -d mysql:8.0 - 创建 `slave` 用户并授权进入数据库docker exec -it mysql_master bash 创建用户授权mysql> CREATE USER ‘slave’@’%’ IDENTIFIED WITH mysql_native_password BY ‘slave’; mysql> GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’; mysql> flush privileges; - 获取主节点当前 `binary log` 文件名和位置 `position`mysql> SHOW MASTER STATUS; +———————-+—————+———————+—————————+—————————————————————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +———————-+—————+———————+—————————+—————————————————————+ | binlog.000003 | 868 | | | 1b009ef8-a67f-11ea-8c9a-0242ac110002:1-8 | +———————-+—————+———————+—————————+—————————————————————+ 1 row in set (0.00 sec) ### 启动从数据库docker run —name mysql_slave —restart=always \ -p 3306:3306 \ -v /var/lib/mysql/my.cnf:/etc/mysql/my.cnf \ -v /var/lib/mysql/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=Root123456 \ -d mysql:8.0 ### 配置主从复制进入数据库docker exec -it mysql_slave bash 主从配置mysql> CHANGE MASTER TO mysql> MASTER_HOST=’192.168.0.10’, mysql> MASTER_USER=’slave’, mysql> MASTER_PASSWORD=’slave’, mysql> MASTER_PORT=3306, mysql> MASTER_LOG_FILE=’binlog.000003’, mysql> MASTER_LOG_POS=868; 开启主从同步mysql> start slave; 再查看主从同步状态mysql> show slave status; 这里只要看到两个参数 `Slave_IO_Running` 和 `Slave_SQL_Running` 都为 **true** 且 **Error** 字段都为空则主从集群正常工作。### 建库在主数据库创建 Rainbond 部署所需的数据库，查看从服务器是否同步更新了数据- 在主库创建库mysql> create database console; mysql> create database region; - 在从库查看mysql> show databases; +——————————+ | Database | +——————————+ | information_schema | | console | | mysql | | performance_schema | | region | | sys | +——————————+ 5 rows in set (0.00 sec) 数据同步成功，则主从复制部署完成## 在 Centos 7 中部署 MySQL 主从集群### 通过 YUM 安装 MySQL在主从服务器中均需要执行#### 下载 rpm 包wget http://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm rpm -ivh mysql80-community-release-el7-1.noarch.rpm #### 卸载 mariadbrpm -qa|grep mariadb rpm -e mariadb-libs-5.5.65-1.el7.x86_64 —nodeps #### 安装 MySQLyum -y install mysql-server ##### danger如果出现 `mysql-community-client-8.0.28-1.el7.x86_64.rpm` 的公钥尚未安装，在安装之前执行 `rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022`#### 启动 MySQLsystemctl restart mysqld systemctl enable mysqld #### 获取 MySQL 密码并修改和配置远程访问获取密码grep “A temporary password is generated for root@localhost” /var/log/mysqld.log 登录数据库mysql -u root -p 修改密码alter user ‘root’@’localhost’ identified by ‘Root123456!’; 开启远程访问update mysql.user set host = ‘%’ where user = ‘root’; alter user ‘root’@’%’ identified by ‘Root123456!’ password expire never; alter user ‘root’@’%’ identified with mysql_native_password by ‘Root123456!’; flush privileges; ### 主从数据库配置文件#### 修改主和从节点配置文件主数据库配置文件vim /etc/my.cnfcharacter-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci 默认存储引擎default-storage-engine=INNODB # Compatible with versions before 8.0default_authentication_plugin=mysql_native_password skip-host-cache skip-name-resolve 主从配置log-bin=binlog server-id=1 gtid-mode=on enforce-gtid-consistency=on log-slave-updates=on [client] 设置客户端编码default-character-set=utf8mb4 [mysql] 设置mysql客户端默认编码default-character-set=utf8mb4 vim /etc/my.cnf 修改为utf8编码character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci 默认存储引擎default-storage-engine=INNODB Compatible with versions before 8.0default_authentication_plugin=mysql_native_password skip-host-cache skip-name-resolve 主从配置server-id=2 gtid-mode=on enforce-gtid-consistency=on log-slave-updates=on [client] 设置客户端编码default-character-set=utf8mb4 [mysql] 设置mysql客户端默认编码default-character-set=utf8mb4 重启主和从数据库systemctl restart mysqld ### 配置主从复制#### 在主节点创建slave账号CREATE USER ‘slave’@’%’ IDENTIFIED WITH mysql_native_password BY ‘slave’; GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’; flush privileges; #### 获取主节点当前 binlog 和 positionSHOW MASTER STATUS; +———————-+—————+———————+—————————+—————————————————————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +———————-+—————+———————+—————————+—————————————————————+ | binlog.000003 | 868 | | | 1b009ef8-a67f-11ea-8c9a-0242ac110002:1-8 | +———————-+—————+———————+—————————+—————————————————————+ #### 配置从节点连接主节点mysql> CHANGE MASTER TO mysql> MASTER_HOST=’192.168.0.10’, mysql> MASTER_USER=’slave’, mysql> MASTER_PASSWORD=’slave’, mysql> MASTER_PORT=3306, mysql> MASTER_LOG_FILE=’binlog.000003’, mysql> MASTER_LOG_POS=868; 启动从节点mysql> start slave; 再查看主从同步状态mysql> show slave status; 这里只要看到两个参数 `Slave_IO_Running` 和 `Slave_SQL_Running` 都为 **true** 且 **Error** 字段都为空则主从集群正常工作。### 建库在主数据库创建 Rainbond 部署所需的数据库，查看从服务器是否同步更新了数据- 在主库创建库mysql> create database console; mysql> create database region; - 在从库查看mysql> show databases; +——————————+ | Database | +——————————+ | information_schema | | console | | mysql | | performance_schema | | region | | sys | +——————————+ 5 rows in set (0.00 sec) ``` 数据同步成功，则主从复制部署完成 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |