您好,欢迎来到站长目录(28sn.com)!


MySQL所有的主从同步架构搭建方式

来源:网络整理 浏览:330次 时间:2021-10-27
一.前言

本文将指导搭建所有的MySQL主从同步架构方案:

  • 一主多从架构
  • 主主双向同步架构
  • M-S-S三级级联同步架构
  • 多主多从架构
二.关于MySQL主从同步

MySQL主从同步是构建大型,高性能应用的基础,MySQL主从同步可以实现在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力(主库写,从库读,降压),在从主服务器进行备份,避免备份期间影响主服务器服务(确保数据安全),当主服务器出现问题时,可以切换到从服务器(提升性能)。

三.部署规划3.1 服务器规划服务器操作系统版本CPU架构MySQL版本node6CentOS Linux release 7.4.1708x86_645.7.26node7CentOS Linux release 7.4.1708x86_645.7.26node8CentOS Linux release 7.4.1708x86_645.7.26node9CentOS Linux release 7.4.1708x86_645.7.263.2 数据库目录规划文件类型文件部署位置数据目录datadir/data/data(/data目录请确保足够大)配置文件my.cnf/etc/my.cnf错误日志log-error/data/log/mysql_error.log二进制日志log-bin/data/binlogs/mysql-bin(用于数据库恢复和主从复制,以及审计(audit)操作)慢查询日志slow_query_log_file/data/log/mysql_slow_query.log套接字文件socket/data/run/mysql.sock进程ID文件mysql.pid/data/run/mysql.pid四.准备工具

1.MySQL通用二进制包:mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

下载地址:https://dev.mysql.com/downloads/mysql/5.7.html#downloads

 

五.四台机器上使用通用二进制包安装MySQL(以node7为例)5.1 上传MySQL通用二进制安装包到node7的/usr/local/src目录下
[root@node7 src]# pwd/usr/local/src[root@node7 src]# lsmysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
5.2 解压MySQL到指定目录并改名
[root@node7 src]# tar -zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[root@node7 src]# cd /usr/local/[root@node7 local]# lsbin  etc  games  include  lib  lib64  libexec  mysql-5.7.26-linux-glibc2.12-x86_64  sbin  share  src[root@node7 local]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql[root@node7 local]# lsbin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
5.3 创建MySQL用户和用户组
[root@node7 local]# groupadd -g 1111 mysql[root@node7 local]# useradd -g mysql -u 1111 -s /sbin/nologin mysql[root@node7 local]# id mysql    #查看用户信息uid=1111(mysql) gid=1111(mysql) groups=1111(mysql)
5.4 配置MySQL的bin目录到PATH路径
[root@node7 local]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile[root@node7 local]# source /etc/profile[root@node7 local]# mysql    #输入MySQL之后双击tab键,即可列出候选MySQL命令mysql                       mysql_client_test_embedded  mysqld-debug                mysqldumpslow               mysql_plugin                mysqlslap                   mysql_upgrademysqladmin                  mysql_config                mysqld_multi                mysql_embedded              mysqlpump                   mysql_ssl_rsa_setup         mysqlxtestmysqlbinlog                 mysql_config_editor         mysqld_safe                 mysqlimport                 mysql_secure_installation   mysqltest_embedded          mysqlcheck                  mysqld                      mysqldump                   mysql_install_db            mysqlshow                   mysql_tzinfo_to_sql
5.5 创建MySQL数据存放目录
[root@node7 ~]# mkdir -p /data/{data,log,binlogs,run}[root@node7 ~]# tree /data    #如果没有tree命令,则yum -y install tree安装/data├── binlogs├── data├── log└── run4 directories, 0 files[root@node7 ~]# chown -R mysql:mysql /data[root@node7 ~]# ll /data/total 0drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 binlogsdrwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 datadrwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 logdrwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 run
5.6 配置MySQL配置文件
[root@node7 mysql]# rm -rf /etc/my.cnf[root@node7 mysql]# touch /etc/my.cnf#my.cnf配置文件详解,请查看我上一篇blog的#https://www.cnblogs.com/renshengdezheli/p/11913248.html的“MySQL配置文件优化参考”[root@node7 mysql]# cat /etc/my.cnf[client]port=3306socket=/data/run/mysql.sock[mysqld]port=3306socket=/data/run/mysql.sockpid_file=/data/run/mysql.piddatadir=/data/datadefault_storage_engine=InnoDBmax_allowed_packet=512Mmax_connections=2048open_files_limit=65535skip-name-resolvelower_case_table_names=1character-set-server=utf8mb4collation-server=utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'innodb_buffer_pool_size=1024Minnodb_log_file_size=2048Minnodb_file_per_table=1innodb_flush_log_at_trx_commit=0key_buffer_size=64Mlog-error=/data/log/mysql_error.loglog-bin=/data/binlogs/mysql-binslow_query_log=1slow_query_log_file=/data/log/mysql_slow_query.loglong_query_time=5tmp_table_size=32Mmax_heap_table_size=32Mquery_cache_type=0query_cache_size=0server-id=1
5.7 初始化MySQL数据库
[root@node7 mysql]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/data[root@node7 mysql]# echo $?0[root@node7 mysql]# grep 'temporary password' /data/log/mysql_error.log    #查看MySQL初始化密码2019-12-03T03:47:42.639938Z 1 [Note] A temporary password is generated for root@localhost: lhrh>J,p<8gw
5.8 生成ssl(可选)
#关于MySQL开启ssl查看https://www.cnblogs.com/mysql-dba/p/7061300.html[root@node7 mysql]# mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/data/dataGenerating a 2048 bit RSA private key......................................+++.+++writing new private key to 'ca-key.pem'-----Generating a 2048 bit RSA private key....................................+++............................+++writing new private key to 'server-key.pem'-----Generating a 2048 bit RSA private key.....................................................................................+++..............................................+++writing new private key to 'client-key.pem'-----#执行完成之后,会有在datadir目录生成*.pem文件[root@node7 mysql]# ls /data/data/auto.cnf    client-cert.pem  ibdata1      mysql               public_key.pem   sysca-key.pem  client-key.pem   ib_logfile0  performance_schema  server-cert.pemca.pem      ib_buffer_pool   ib_logfile1  private_key.pem     server-key.pem
5.9 配置MySQL启动项并设置开机自启动5.9.1 centos6版本
cd /usr/local/mysqlcp support-files/mysql.server /etc/init.d/mysql.serverchkconfig --add mysql.serverchkconfig  mysql.server onchkconfig --list
5.9.2 centos7版本
[root@node7 system]# cd /usr/lib/systemd/system[root@node7 system]# touch mysqld.service [root@node7 system]# vim mysqld.service [root@node7 system]# cat mysqld.service # Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA## systemd service file for MySQL forking server#[Unit]Description=MySQL ServerDocumentation=man:mysqld(5.7)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlType=forkingPIDFile=/data/run/mysql.pid# Disable service start and stop timeout logic of systemd for mysqld service.TimeoutSec=0# Execute pre and post scripts as rootPermissionsStartOnly=true# Needed to create system tables#ExecStartPre=/usr/bin/mysqld_pre_systemd# Start main serviceExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS# Use this to switch malloc implementationEnvironmentFile=-/etc/sysconfig/mysql# Sets open_files_limitLimitNOFILE = 65535Restart=on-failureRestartPreventExitStatus=1PrivateTmp=false[root@node7 system]# systemctl daemon-reload    #重新加载服务配置文件[root@node7 system]# systemctl enable mysqld    #设置MySQL开机自启动Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.[root@node7 system]# systemctl is-enabled mysqld   #查看MySQL开机自启动是否设置成功enabled
5.10 启动MySQL
[root@node7 system]# systemctl start mysqld[root@node7 system]# systemctl status mysqld    #查看MySQL启动状态● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since Tue 2019-12-03 14:42:14 CST; 9s ago     Docs: man:mysqld(5.7)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 2905 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Main PID: 2907 (mysqld)   CGroup: /system.slice/mysqld.service           └─2907 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pidDec 03 14:42:13 node7 systemd[1]: Starting MySQL Server...Dec 03 14:42:14 node7 systemd[1]: Started MySQL Server.[root@node7 system]# ps -ef | grep mysql         #查看MySQL进程mysql      2907      1  2 14:42 ?        00:00:00 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pidroot       2942   2576  0 14:42 pts/0    00:00:00 grep --color=auto mysql
5.11 进行MySQL安全初始化(可选)
[root@node7 system]# mysql_secure_installation Securing the MySQL server deployment.Enter password for user root:    #这里输入MySQL初始化时生成的密码(grep 'temporary password' /data/log/mysql_error.log)The existing password for the user account root has expired. Please set a new password.New password:   #输入新密码Re-enter new password: VALIDATE PASSWORD PLUGIN can be used to test passwordsand improve security. It checks the strength of passwordand allows the users to set only those passwords which aresecure enough. Would you like to setup VALIDATE PASSWORD plugin?Press y|Y for Yes, any other key for No: n   #y安装MySQL密码插件Using existing password for root.Change the password for root ? ((Press y|Y for Yes, any other key for No) : n ... skipping.By default, a MySQL installation has an anonymous user,allowing anyone to log into MySQL without having to havea user account created for them. This is intended only fortesting, and to make the installation go a bit smoother.You should remove them before moving into a productionenvironment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : y  #y移除匿名用户Success.Normally, root should only be allowed to connect from'localhost'. This ensures that someone cannot guess atthe root password from the network.Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n  #是否允许root远程登录 ... skipping.By default, MySQL comes with a database named 'test' thatanyone can access. This is also intended only for testing,and should be removed before moving into a productionenvironment.Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y  #是否移除test数据库 - Dropping test database...Success. - Removing privileges on test database...Success.Reloading the privilege tables will ensure that all changesmade so far will take effect immediately.Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y  #刷新权限表Success.All done!
5.12 修改密码,给用户赋权限(根据自己情况赋权限)
[root@node7 ~]# mysql -uroot -p123456mysql> SET PASSWORD = PASSWORD('123456');#修改root密码为123456,如果提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements,则说明密码设置太简单,如果想设置123456这样的简单密码,可在SQL中执行:#mysql> set global validate_password_policy=0;#mysql> set global validate_password_length=1;#这样再次执行SET PASSWORD = PASSWORD('123456')就可成功。Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> UPDATE mysql.user SET authentication_string =PASSWORD('123456') WHERE User='mysql';    #修改MySQL的mysql用户的密码为123456Query OK, 0 rows affected, 1 warning (0.00 sec)Rows matched: 0  Changed: 0  Warnings: 1mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;   Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;   #赋予mysql用户可以在任何机器上登录,并拥有所有表的所有权限Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> FLUSH PRIVILEGES ;   #刷新权限,让修改立即生效Query OK, 0 rows affected (0.00 sec)mysql> exit;Bye----------------------------------------------------------------------------------------------------------------------------------------------------------------------------#以下是为MySQL赋权限的介绍mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。当权限1,权限2,…权限n被all privileges或者all代替,表示赋予用户全部权限。当数据库名称.表名称被*.*代替,表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost,也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。‘连接口令’不能为空,否则创建失败。比如:mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;给来自10.163.225.87的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限,并设定口令为123。 mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;给来自10.163.225.87的用户joe分配可对数据库vtdc所有表进行所有操作的权限,并设定口令为123。----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5.13 导入时区信息到MySQL库
[root@node7 system]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p123456 mysql#执行上述操作之后,time_zone,time_zone_leap_second,time_zone_name,time_zone_transition   ,time_zone_transition_type表就有时区数据了[root@node7 system]# mysql -uroot -p123456 mysqlmysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || engine_cost               || event                     || func                      || general_log               || gtid_executed             || help_category             || help_keyword              || help_relation             || help_topic                || innodb_index_stats        || innodb_table_stats        || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || proxies_priv              || server_cost               || servers                   || slave_master_info         || slave_relay_log_info      || slave_worker_info         || slow_log                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+31 rows in set (0.00 sec)
5.14 查看MySQL版本信息
[root@node7 system]# mysql -Vmysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper[root@node7 system]# mysqladmin version -uroot -p123456mysqladmin: [Warning] Using a password on the command line interface can be insecure.mysqladmin  Ver 8.42 Distrib 5.7.26, for linux-glibc2.12 on x86_64Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Server version5.7.26-logProtocol version10ConnectionLocalhost via UNIX socketUNIX socket/data/run/mysql.sockUptime:31 min 53 secThreads: 1  Questions: 8855  Slow queries: 0  Opens: 214  Flush tables: 1  Open tables: 203  Queries per second avg: 4.628
5.15 如果防火墙开着,则需要开放3306端口
[root@node7 system]# systemctl status firewalld● firewalld.service - firewalld - dynamic firewall daemon   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)   Active: active (running) since Tue 2019-12-03 15:22:18 CST; 3s ago     Docs: man:firewalld(1) Main PID: 3343 (firewalld)   CGroup: /system.slice/firewalld.service           └─3343 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopidDec 03 15:22:17 node7 systemd[1]: Starting firewalld - dynamic firewall daemon...Dec 03 15:22:18 node7 systemd[1]: Started firewalld - dynamic firewall daemon.Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'beyond-scope' is not supported by the kernel for ipv6.Dec 03 15:22:18 node7 firewalld[3343]: WARNING: beyond-scope: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'failed-policy' is not supported by the kernel for ipv6.Dec 03 15:22:18 node7 firewalld[3343]: WARNING: failed-policy: INVALID_ICMPTYPE: No supported ICMP type., ignorin...-time.Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'reject-route' is not supported by the kernel for ipv6.Dec 03 15:22:18 node7 firewalld[3343]: WARNING: reject-route: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.Hint: Some lines were ellipsized, use -l to show in full.#添加防火墙规则[root@node7 system]# firewall-cmd --permanent --zone=public --add-port=3306/tcpsuccess#重新加载防火墙规则[root@node7 system]# firewall-cmd --reloadsuccess#检查规则是否设置生效[root@node7 system]# firewall-cmd --zone=public --query-port=3306/tcpyes#列出防火墙所有开放的端口[root@node7 system]# firewall-cmd --list-allpublic (active)  target: default  icmp-block-inversion: no  interfaces: ens33  sources:   services: ssh dhcpv6-client  ports: 3306/tcp  protocols:   masquerade: no  forward-ports:   source-ports:   icmp-blocks:   rich rules:
5.16 利用logrotate对MySQL日志进行轮转(日志自动备份切割)
#logrotate配置详解请查看:https://www.linuxidc.com/Linux/2019-02/157099.htm[root@node7 ~]# touch /root/.my.cnf[root@node7 ~]# vim /root/.my.cnf [root@node7 ~]# cat /root/.my.cnf [mysqladmin]  password=123456user=root[root@node7 ~]# chmod 600 /root/.my.cnf [root@node7 ~]# cp /usr/local/mysql/support-files/mysql-log-rotate /etc/logrotate.d/[root@node7 ~]# chmod 644 /etc/logrotate.d/mysql-log-rotate [root@node7 ~]# vim /etc/logrotate.d/mysql-log-rotate [root@node7 ~]# cat /etc/logrotate.d/mysql-log-rotate # The log file name and location can be set in# /etc/my.cnf by setting the "log-error" option# in either [mysqld] or [mysqld_safe] section as# follows:## [mysqld]# log-error=/usr/local/mysql/data/mysqld.log## In case the root user has a password, then you# have to create a /root/.my.cnf configuration file# with the following content:## [mysqladmin]# password = <secret> # user= root## where "<secret>" is the password. ## ATTENTION: The /root/.my.cnf file should be readable# _ONLY_ by root !/data/log/mysql_*.log {        # create 600 mysql mysql        notifempty  #当日志文件为空时,不进行轮转        daily  #默认每一天执行一次rotate轮转工作        rotate 52  #保留多少个日志文件(轮转几次).默认保留四个.就是指定日志文件删除之前轮转的次数,0 指没有备份,此处表示保留52天的日志        missingok   #如果日志文件丢失,不要显示错误        compress    #通过gzip 压缩转储以后的日志    postrotate   #执行的指令# just if mysqld is really runningif test -x /usr/local/mysql/bin/mysqladmin && \   /usr/local/mysql/bin/mysqladmin ping &>/dev/nullthen   /usr/local/mysql/bin/mysqladmin flush-logsfi    endscript}[root@node7 ~]# [root@node7 ~]# logrotate -fv /etc/logrotate.d/mysql-log-rotate #强制进行日志轮转reading config file /etc/logrotate.d/mysql-log-rotateAllocating hash table for state file, size 15360 BHandling 1 logsrotating pattern: /data/log/mysql_*.log  forced from command line (52 rotations)empty log files are not rotated, old logs are removedconsidering log /data/log/mysql_error.log  log needs rotatingconsidering log /data/log/mysql_slow_query.log  log needs rotatingrotating log /data/log/mysql_error.log, log->rotateCount is 52dateext suffix '-20191203'glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'renaming /data/log/mysql_error.log.52.gz to /data/log/mysql_error.log.53.gz (t -- won't try to dispose of it.................renaming /data/log/mysql_slow_query.log to /data/log/mysql_slow_query.log.1running postrotate scriptcompressing log with: /bin/gzip[root@node7 ~]# [root@node7 ~]# echo $?0#此时查看日志目录,发现日志已经进行轮转,并压缩[root@node7 ~]# ls /data/log/mysql_error.log  mysql_error.log.1.gz  mysql_slow_query.log  mysql_slow_query.log.1.gz

自此,node7上MySQL安装完毕,node6,node8,node9上的MySQL也按照此方法安装。

安装MySQL是进行主从同步,读写分离,分表分库配置的基础,只有安装了MySQL才能进行接下来的操作。

六.MySQL主从同步之一主多从架构6.1 服务器规划主机名IP操作系统版本MySQL版本角色node7192.168.110.188CentOS 7.4.17085.7.26master(主)node8192.168.110.186CentOS 7.4.17085.7.26slave(从)node9192.168.110.187CentOS 7.4.17085.7.26slave(从)6.2 主从同步的原理

master将改变记录到二进制日志(binary log)中,slave将master的binary log events拷贝到它的中继日志(relay log),slave重做中继日志中的事件,修改salve上的数据。

6.3 部署MySQL主从同步之一主多从6.3.1 配置主数据库服务器node76.3.1.1 创建需要同步的数据库及其表
[root@node7 ~]# mysql -uroot -p123456mysql> create database hotdata;     #创建热点数据库Query OK, 1 row affected (0.70 sec)mysql> use hotdata;Database changed#创建顾客表mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));Query OK, 0 rows affected (0.44 sec)mysql> desc customers;   #查看表结构+--------------+-------------+------+-----+---------+-------+| Field        | Type        | Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| cust_id      | int(11)     | YES  |     | NULL    |       || cust_name    | varchar(30) | YES  |     | NULL    |       || cust_address | varchar(50) | YES  |     | NULL    |       || cust_city    | varchar(30) | YES  |     | NULL    |       || cust_state   | varchar(50) | YES  |     | NULL    |       || cust_email   | varchar(30) | YES  |     | NULL    |       || cust_country | varchar(50) | YES  |     | NULL    |       |+--------------+-------------+------+-----+---------+-------+7 rows in set (0.36 sec)mysql> exitBye
6.3.1.2 修改MySQL配置文件
#先关闭数据库再修改MySQL配置文件[root@node7 ~]# systemctl stop mysqld[root@node7 ~]# systemctl status mysqld● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: inactive (dead) since Thu 2019-12-05 10:59:38 CST; 8s ago     Docs: man:mysqld(5.7)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 6777 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Main PID: 6779 (code=exited, status=0/SUCCESS)Dec 05 10:35:44 node7 systemd[1]: Starting MySQL Server...Dec 05 10:36:07 node7 systemd[1]: Started MySQL Server.Dec 05 10:59:36 node7 systemd[1]: Stopping MySQL Server...Dec 05 10:59:38 node7 systemd[1]: Stopped MySQL Server.#修改好的配置文件如下,主从同步相关的配置都放在“#mysql replication”下面[root@node7 ~]# vim /etc/my.cnf[root@node7 ~]# cat /etc/my.cnf[client]port=3306socket=/data/run/mysql.sock[mysqld]port=3306socket=/data/run/mysql.sockpid_file=/data/run/mysql.piddatadir=/data/datadefault_storage_engine=InnoDBmax_allowed_packet=512Mmax_connections=2048open_files_limit=65535skip-name-resolvelower_case_table_names=1character-set-server=utf8mb4collation-server=utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'innodb_buffer_pool_size=1024Minnodb_log_file_size=2048Minnodb_file_per_table=1innodb_flush_log_at_trx_commit=0key_buffer_size=64Mlog-error=/data/log/mysql_error.logslow_query_log=1slow_query_log_file=/data/log/mysql_slow_query.loglong_query_time=5tmp_table_size=32Mmax_heap_table_size=32Mquery_cache_type=0query_cache_size=0skip_ssl#mysql replication,主从同步配置#logbin参数启用二进制日志,并把二进制日志放在/data/binlogs目录下log-bin=/data/binlogs/mysql-bin  #数据库标志ID,唯一server-id=1#binlog-do-db可以被从服务器复制的库binlog-do-db=hotdata#binlog-ignore-db不可以被从服务器复制的库binlog-ignore-db=mysql[root@node7 ~]# systemctl restart mysqld   #重启MySQL数据库
6.3.1.3 主库给从库授予replication权限
[root@node7 ~]# mysql -uroot -p123456#授予node8从库replication权限mysql> grant replication slave on *.* to slave@192.168.110.186 identified by "123456";Query OK, 0 rows affected, 1 warning (0.11 sec)#授予node9从库replication权限mysql> grant replication slave on *.* to slave@192.168.110.187 identified by "123456";Query OK, 0 rows affected, 1 warning (0.01 sec)#刷新权限mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)#查看master状态信息mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000012 |      902 | hotdata      | mysql            |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> exitBye#查看二进制日志[root@node7 ~]# ll /data/binlogs/total 2896-rw-r----- 1 mysql mysql     177 Dec  3 11:47 mysql-bin.000001-rw-r----- 1 mysql mysql 2915818 Dec  3 16:38 mysql-bin.000002-rw-r----- 1 mysql mysql     201 Dec  3 16:38 mysql-bin.000003-rw-r----- 1 mysql mysql     177 Dec  3 17:09 mysql-bin.000004-rw-r----- 1 mysql mysql     177 Dec  3 17:14 mysql-bin.000005-rw-r----- 1 mysql mysql     177 Dec  3 17:25 mysql-bin.000006-rw-r----- 1 mysql mysql    1220 Dec  4 03:12 mysql-bin.000007-rw-r----- 1 mysql mysql     201 Dec  4 03:12 mysql-bin.000008-rw-r----- 1 mysql mysql     177 Dec  4 10:49 mysql-bin.000009-rw-r----- 1 mysql mysql    1743 Dec  5 10:35 mysql-bin.000010-rw-r----- 1 mysql mysql     665 Dec  5 10:59 mysql-bin.000011-rw-r----- 1 mysql mysql     902 Dec  5 11:47 mysql-bin.000012-rw-r----- 1 mysql mysql     372 Dec  5 11:40 mysql-bin.index[root@node7 ~]# mysql -uroot -p123456##查看二进制日志事件mysql> show binlog events\G*************************** 1. row ***************************   Log_name: mysql-bin.000001        Pos: 4 Event_type: Format_desc  Server_id: 1End_log_pos: 123       Info: Server ver: 5.7.26-log, Binlog ver: 4*************************** 2. row ***************************   Log_name: mysql-bin.000001        Pos: 123 Event_type: Previous_gtids  Server_id: 1End_log_pos: 154       Info: *************************** 3. row ***************************   Log_name: mysql-bin.000001        Pos: 154 Event_type: Stop  Server_id: 1End_log_pos: 177       Info: 3 rows in set (0.00 sec)mysql> exitBye
6.3.1.4 备份主库需要从库同步的数据库hotdata
#备份数据库hotdata[root@node7 ~]# mysqldump -uroot -p123456 hotdata >hotdata.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.#给从库分发备份好的数据库[root@node7 ~]# scp hotdata.sql root@192.168.110.186:~/hotdata.sql                                        100% 2239   510.9KB/s   00:00    [root@node7 ~]# scp hotdata.sql root@192.168.110.187:~/hotdata.sql                                        100% 2239   382.8KB/s   00:00
6.3.2 配置从数据库服务器node86.3.2.1 检查数据库版本
#主从数据库版本不一致的话会出现问题[root@node8 ~]# mysql -uroot -p123456mysql> show variables like "%version%";+-------------------------+------------------------------+| Variable_name           | Value                        |+-------------------------+------------------------------+| innodb_version          | 5.7.26                       || protocol_version        | 10                           || slave_type_conversions  |                              || tls_version             | TLSv1,TLSv1.1                || version                 | 5.7.26-log                   || version_comment         | MySQL Community Server (GPL) || version_compile_machine | x86_64                       || version_compile_os      | linux-glibc2.12              |+-------------------------+------------------------------+8 rows in set (0.01 sec)mysql> quitBye
6.3.2.2 测试连接到主服务器是否成功
[root@node8 ~]# mysql -uslave -p123456 -h 192.168.110.188#只有复制的权限, 是看不到其他库的。mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)mysql> exitBye
6.3.2.3 导入hotdata数据库,和主数据库保持一致
[root@node8 ~]# mysql -uroot -p123456mysql> create database hotdata;Query OK, 1 row affected (0.00 sec)mysql> exitBye#导入hotdata表[root@node8 ~]# mysql -uroot -p123456 hotdata<hotdata.sql mysql: [Warning] Using a password on the command line interface can be insecure.
6.3.2.4 修改配置文件
[root@node8 ~]# systemctl stop mysqld[root@node8 ~]# vim /etc/my.cnf[root@node8 ~]# cat /etc/my.cnf[client]port=3306socket=/data/run/mysql.sock[mysqld]port=3306socket=/data/run/mysql.sockpid_file=/data/run/mysql.piddatadir=/data/datadefault_storage_engine=InnoDBmax_allowed_packet=512Mmax_connections=2048open_files_limit=65535skip-name-resolvelower_case_table_names=1character-set-server=utf8mb4collation-server=utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'innodb_buffer_pool_size=1024Minnodb_log_file_size=2048Minnodb_file_per_table=1innodb_flush_log_at_trx_commit=0key_buffer_size=64Mlog-error=/data/log/mysql_error.logslow_query_log=1slow_query_log_file=/data/log/mysql_slow_query.loglong_query_time=5tmp_table_size=32Mmax_heap_table_size=32Mquery_cache_type=0query_cache_size=0#mysql replication配置#server-id必须唯一server-id=2#下面log-bin,binlog-do-db,binlog-ignore-db这三个参数都不是必须的log-bin=/data/binlogs/mysql-binbinlog-do-db=hotdatabinlog-ignore-db=mysql[root@node8 ~]# [root@node8 ~]# systemctl restart mysqld
6.3.2.5 从库设置slave复制主库数据
[root@node8 ~]# mysql -uroot -p123456mysql> stop slave;    #停止slaveQuery OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host='192.168.110.188',master_user='slave',master_password='123456';Query OK, 0 rows affected, 2 warnings (0.02 sec)#释义:#change master to #master_host='192.168.0.68',master_user='root',master_password='root',master_log_file='#mysql-bin.000004', master_log_pos=28125;#上面的master_log_file是在Master中show master status显示的File,而master_log_pos是在Master中#show master status显示的Position。#也可以通过show slave status查看配置信息,如果没有同步成功,比对show slave status中的position和#file是否和show master status中的对应。mysql> start slave;   #启动slaveQuery OK, 0 rows affected (0.01 sec)#查看slave状态mysql> show slave status\G     *************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.110.188                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000012          Read_Master_Log_Pos: 902               Relay_Log_File: node8-relay-bin.000010                Relay_Log_Pos: 519        Relay_Master_Log_File: mysql-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: No              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 1062                   Last_Error: Could not execute Update_rows event on table mysql.user; Duplicate entry '%-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 942                 Skip_Counter: 0          Exec_Master_Log_Pos: 306              Relay_Log_Space: 7216              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1062               Last_SQL_Error: Could not execute Update_rows event on table mysql.user; Duplicate entry '%-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 942  Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226             Master_Info_File: /data/data/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp: 191205 15:18:40               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)#可以看到Last_Error报错了,是因为主键重复了,按照下面操作即可mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> SET GLOBAL sql_slave_skip_counter =1;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)#再次查看slave状态,如果Last_Error没报错,并且Slave_IO_Running和Slave_SQL_Running都为yes则说明#配置成功了#Slave_IO_Running :负责与主机的IO通信#Slave_SQL_Running:负责自己的slave mysql进程#如果执行了stop slave,SET GLOBAL sql_slave_skip_counter =1,start slave之后,show slave #status\G还是报错,则再次执行一遍stop slave,SET GLOBAL sql_slave_skip_counter =1,start #slave即可,最多执行3遍,即可消除所有错误。mysql> show slave status\G   *************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.110.188                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000013          Read_Master_Log_Pos: 154               Relay_Log_File: node8-relay-bin.000037                Relay_Log_Pos: 320        Relay_Master_Log_File: mysql-bin.000013             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: 154              Relay_Log_Space: 693              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: 0Master_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                  Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226             Master_Info_File: /data/data/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)#查看数据目录,可以发现Relay_Log_File[root@node8 ~]# ls /data/data/auto.cnf        ibdata1      ibtmp1       node8-relay-bin.000036  performance_schemahotdata         ib_logfile0  master.info  node8-relay-bin.000037  relay-log.infoib_buffer_pool  ib_logfile1  mysql        node8-relay-bin.index   sys
6.3.3 配置从数据库服务器node9

node9的配置和node8一样,要注意的是配置文件my.cnf里server-id必须唯一,不能和node7,node8相同。

6.3.4 在主服务器上查看状态
[root@node7 ~]# mysql -uroot -p123456#可以看到有两个slavemysql> show processlist\G*************************** 1. row ***************************     Id: 8   User: slave   Host: 192.168.110.186:49414     db: NULLCommand: Binlog Dump   Time: 4313  State: Master has sent all binlog to slave; waiting for more updates   Info: NULL*************************** 2. row ***************************     Id: 10   User: slave   Host: 192.168.110.187:33510     db: NULLCommand: Binlog Dump   Time: 4208  State: Master has sent all binlog to slave; waiting for more updates   Info: NULL*************************** 3. row ***************************     Id: 11   User: root   Host: localhost     db: NULLCommand: Query   Time: 0  State: starting   Info: show processlist3 rows in set (0.00 sec)
6.3.5 插入数据测试主从同步
#在主服务器上插入数据mysql> use hotdata;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> insert into customers values (1,'张三','珠江新城','广州','广东省','1234567890@qq.com','china');Query OK, 1 row affected (0.10 sec)mysql> insert into customers values (2,'李四','天安门','北京','北京市','1234127890@qq.com','china');Query OK, 1 row affected (0.04 sec)mysql> insert into customers values (3,'王二麻子','钟鼓楼','昆明','云南省','1234567870@qq.com','china');Query OK, 1 row affected (0.01 sec)mysql> insert into customers values (4,'赵四','百花广场','佛山','广东省','1239867890@qq.com','china');Query OK, 1 row affected (0.00 sec)mysql> insert into customers values (5,'刘能','体育中心','广州','广东省','1234512890@qq.com','china');Query OK, 1 row affected (0.00 sec)mysql> insert into customers values (6,'谢广坤','体育西路','广州','广东省','1364567890@qq.com','china');Query OK, 1 row affected (0.00 sec)mysql> select * from customers;   #查看数据+---------+--------------+--------------+-----------+------------+-------------------+--------------+| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |+---------+--------------+--------------+-----------+------------+-------------------+--------------+|       1 | 张三         | 珠江新城     | 广州      | 广东省     | 1234567890@qq.com | china        ||       2 | 李四         | 天安门       | 北京      | 北京市     | 1234127890@qq.com | china        ||       3 | 王二麻子     | 钟鼓楼       | 昆明      | 云南省     | 1234567870@qq.com | china        ||       4 | 赵四         | 百花广场     | 佛山      | 广东省     | 1239867890@qq.com | china        ||       5 | 刘能         | 体育中心     | 广州      | 广东省     | 1234512890@qq.com | china        ||       6 | 谢广坤       | 体育西路     | 广州      | 广东省     | 1364567890@qq.com | china        |+---------+--------------+--------------+-----------+------------+-------------------+--------------+6 rows in set (0.01 sec)mysql> exitBye#在两个slave上查看数据[root@node8 ~]# mysql -uroot -p123456#在node8上查看数据,发现数据已经同步mysql> select * from hotdata.customers;+---------+--------------+--------------+-----------+------------+-------------------+--------------+| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |+---------+--------------+--------------+-----------+------------+-------------------+--------------+|       1 | 张三         | 珠江新城     | 广州      | 广东省     | 1234567890@qq.com | china        ||       2 | 李四         | 天安门       | 北京      | 北京市     | 1234127890@qq.com | china        ||       3 | 王二麻子     | 钟鼓楼       | 昆明      | 云南省     | 1234567870@qq.com | china        ||       4 | 赵四         | 百花广场     | 佛山      | 广东省     | 1239867890@qq.com | china        ||       5 | 刘能         | 体育中心     | 广州      | 广东省     | 1234512890@qq.com | china        ||       6 | 谢广坤       | 体育西路     | 广州      | 广东省     | 1364567890@qq.com | china        |+---------+--------------+--------------+-----------+------------+-------------------+--------------+6 rows in set (0.00 sec)mysql> exitBye[root@node9 ~]# mysql -uroot -p123456#在node9上查看数据,发现数据已经同步mysql> select * from hotdata.customers;+---------+--------------+--------------+-----------+------------+-------------------+--------------+| cust_id | cust_name    | cust_address | cust_city | cust_state | cust_email        | cust_country |+---------+--------------+--------------+-----------+------------+-------------------+--------------+|       1 | 张三         | 珠江新城     | 广州      | 广东省     | 1234567890@qq.com | china        ||       2 | 李四         | 天安门       | 北京      | 北京市     | 1234127890@qq.com | china        ||       3 | 王二麻子     | 钟鼓楼       | 昆明      | 云南省     | 1234567870@qq.com | china        ||       4 | 赵四         | 百花广场     | 佛山      | 广东省     | 1239867890@qq.com | china        ||       5 | 刘能         | 体育中心     | 广州      | 广东省     | 1234512890@qq.com | china        ||       6 | 谢广坤       | 体育西路     | 广州      | 广东省     | 1364567890@qq.com | china        |+---------+--------------+--------------+-----------+------------+-------------------+--------------+6 rows in set (0.00 sec)mysql> exitBye

注意:

  • 主从同步,主数据库上添加数据,从数据库上同步,但是从数据库添加数据,主不同步
  • 由于是主从同步,如果主上删除了数据,那么从上的数据也就没了,因此建议在主上做定期备份(mysqldump)

自此,MySQL主从同步之一主多从架构已经搭建完毕。

6.3.6 彻底取消主从同步

既然有搭建主从同步就有撤销主从同步,如果有撤销主从同步的需求,请看下文。

#在主库上执行#重置主记录信息mysql> reset master;Query OK, 0 rows affected (0.00 sec)mysql> show master status\G*************************** 1. row ***************************             File: mysql-bin.000001         Position: 154     Binlog_Do_DB: hotdata Binlog_Ignore_DB: mysqlExecuted_Gtid_Set: 1 row in set (0.00 sec)#在两个从库上执行mysql> stop slave;Query OK, 0 rows affected (0.00 sec)#清空从所有连接、信息记录mysql> reset slave all;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\GEmpty s

推荐站点

  • 我爱发烧音乐我爱发烧音乐

    我爱发烧音乐囊括了从流行音乐到古典音乐多个类型的音乐作品,专栏推荐最新的音乐,提供音乐排名榜单!可供免费线上收听音乐,歌曲流畅,音效极佳! 网站提供的钢琴以及二胡专栏,可供收听者,陶冶情操,改善心情,是难得的轻音乐典藏!

    www.520fs.com
  • 世纪音乐网世纪音乐网

    世纪音乐网是专业的在线音乐试听MP3下载网站。歌曲总计30余万首,收录了网上最新歌曲和流行音乐,DJ舞曲,非主流音乐,经典老歌,劲舞团歌曲,搞笑歌曲,儿童歌曲,英文歌曲等。是您上网听歌的最佳网站。

    www.ssjj.com
  • 杭州网杭州网

      杭州网是杭州地区唯一的新闻门户网站,由中共杭州市委宣传部、杭州日报报业集团和杭州广播电视集团共同组建的杭州网络传媒有限公司运营。

    www.hangzhou.com.cn
  • 深圳在线深圳在线

      深圳在线 www.szol.net是深圳本地最大、最早的地方生活资讯网站之一,网站名“深圳在线www.szol.net”由南方报业传媒集团编辑委员会总编辑、南方日报社总编辑、南方都市报总编辑、南方书画院名誉院长王春芙亲笔题名,深圳在线www.szol.net团队与深圳热线www.szonline.net、奥一网www.oeeee.com都源于全国最早成立于1996年的知名网络公司——深圳万用网。

    www.szol.net
  • 今题网今题网

     今题网- 中国领先的社区服务网,提供社区服务, 在线交友和商家推广服务,于2004年创建上线,公司现有员工超过百名。今题网自成立以来,凭借其独特的定位和丰富的社区交友功能, 凭借其团队超强的搜索引擎优化技术吸引超过千万的用户成为今题网的注册会员。

    www.jinti.com

鄂公网安备 42062502000001号