当前位置:   article > 正文

MySQL一主两从配置_mysql一主2从模式搭建配置

mysql一主2从模式搭建配置

搭建一主两从的mysql5.7.22数据库架构(阿里云ECS云主机centos7.3系统)

10.100.10.190 DB1 master 写入,数据节点
10.100.10.191 DB2 slave1 读,数据节点
10.100.10.192 DB3 slave2 读,数据节点

三个数据库服务器上都关闭selinux,编辑/etc/selinux/config文件,使SELINUX=disabled,
阿里云的ECS云主机默认selinux和防火墙firewall已经设置了关闭。

设置主机名:

  1. 登录192.168.1.102上:
  2. hostnamectl set-hostname DB01
  3. 登录192.168.1.103上:
  4. hostnamectl set-hostname DB02
  5. 登录192.168.1.104上:
  6. hostnamectl set-hostname DB03
  7. 编辑三个主机的/etc/hosts文件
  8. cat >>/etc/hosts <<EOF
  9. 10.100.10.190 DB01
  10. 10.100.10.191 DB02
  11. 10.100.10.192 DB03
  12. EOF

设置三台机器相互之间的免密钥登录
登录到DB1上:

  1. 登录到DB1上:
  2. ssh-keygen -t rsa
  3. ssh-copy-id DB02
  4. ssh-copy-id DB03
  5. 登录到DB2上:
  6. ssh-keygen -t rsa
  7. ssh-copy-id DB01
  8. ssh-copy-id DB03
  9. 登录到DB3上:
  10. ssh-keygen -t rsa
  11. ssh-copy-id DB01
  12. ssh-copy-id DB02

同时连上DB01、DB02、DB03
安装前准备:
卸载centos7系统自带的mariadb,由于我们需要指定mysql数据库的数据存放目录,故不采用yum安装的方法。

  1. rpm -qa |grep mariadb
  2. rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64
  3. # 删除etc目录下数据库的配置文件my.cnf
  4. rm /etc/my.cnf

检查mysql是否存在

  1. rpm -qa |grep mysql
  2. cat /etc/group |grep mysql
  3. cat /etc/passwd |grep mysql
  4. # 如果不存在,创建数据库用户mysql
  5. groupadd -g 1001 mysql #-g:用于指定GID,默认为使用当前组大的GID+1
  6. useradd -m -u 1001 -g mysql mysql #-m:创建用户的家目录 -g GROUP:指定新用户的主组,-u UID:指定新用户的主组

修改DB01的/etc/my.cnf配置文件
对DB01 DB02 DB03的/etc/my.cnf配置文件进行修改
DB01 Master

  1. cat >/etc/my.cnf <<EOF
  2. [mysql]
  3. default-character-set=utf8
  4. [mysqld]
  5. server-id=001
  6. user=mysql
  7. port = 3306
  8. basedir=/usr/local/mysql
  9. datadir=/home/mysql/data
  10. # Logsetting
  11. # relay-log=DB01-relay-bin
  12. # relay-log-index=DB01-relay-bin.index
  13. # read_only=1
  14. log-bin=mysql-bin
  15. binlog-ignore-db=mysql
  16. sync_binlog = 1
  17. max_binlog_size=1024M
  18. max_binlog_cache_size=4096M
  19. binlog_stmt_cache_size=1M
  20. binlog_checksum = none
  21. binlog_format = mixed
  22. binlog_cache_size=4M
  23. expire_logs_days=30
  24. relay_log_purge=0
  25. skip-name-resolve
  26. # InnoDBSetting
  27. innodb_buffer_pool_size=16384M
  28. innodb_log_file_size=512M
  29. innodb_log_buffer_size=16M
  30. innodb_log_files_in_group=3
  31. innodb_flush_log_at_trx_commit=1
  32. innodb_stats_on_metadata=OFF
  33. innodb_flush_method=O_DIRECT
  34. table_open_cache=4096
  35. sort_buffer_size=2M
  36. # 允许最大连接数
  37. max_connections=800
  38. # 服务端使用的字符集默认为8比特编码的latin1字符集
  39. character-set-server=utf8
  40. # 创建新表时将使用的默认存储引擎
  41. default-storage-engine=INNODB
  42. lower_case_table_names=1
  43. max_allowed_packet=16M
  44. EOF

配置DB02从库
修改/etc/my.cnf文件

  1. cat >/etc/my.cnf <<EOF
  2. [mysql]
  3. default-character-set=utf8
  4. [mysqld]
  5. server-id=002
  6. user=mysql
  7. port = 3306
  8. basedir=/usr/local/mysql
  9. datadir=/home/mysql/data
  10. # Logsetting
  11. relay-log=DB02-relay-bin
  12. relay-log-index=DB02-relay-bin.index
  13. #read_only=1
  14. log-bin=mysql-bin
  15. binlog-ignore-db=mysql
  16. sync_binlog = 1
  17. max_binlog_size=1024M
  18. max_binlog_cache_size=4096M
  19. binlog_stmt_cache_size=1M
  20. binlog_checksum = none
  21. binlog_format = mixed
  22. binlog_cache_size=4M
  23. expire_logs_days=30
  24. relay_log_purge=0
  25. skip-name-resolve
  26. # InnoDBSetting
  27. innodb_buffer_pool_size=16384M
  28. innodb_log_file_size=512M
  29. innodb_log_buffer_size=16M
  30. innodb_log_files_in_group=3
  31. innodb_flush_log_at_trx_commit=1
  32. innodb_stats_on_metadata=OFF
  33. innodb_flush_method=O_DIRECT
  34. table_open_cache=4096
  35. sort_buffer_size=2M
  36. # 允许最大连接数
  37. max_connections=800
  38. # 服务端使用的字符集默认为8比特编码的latin1字符集
  39. character-set-server=utf8
  40. # 创建新表时将使用的默认存储引擎
  41. default-storage-engine=INNODB
  42. lower_case_table_names=1
  43. max_allowed_packet=16M
  44. EOF

配置DB03从库
修改/etc/my.cnf文件

  1. cat >/etc/my.cnf <<EOF
  2. [mysql]
  3. default-character-set=utf8
  4. [mysqld]
  5. server-id=003
  6. user=mysql
  7. port = 3306
  8. basedir=/usr/local/mysql
  9. datadir=/home/mysql/data
  10. # Logsetting
  11. relay-log=DB03-relay-bin
  12. relay-log-index=DB03-relay-bin.index
  13. #read_only=1
  14. log-bin=mysql-bin
  15. binlog-ignore-db=mysql
  16. sync_binlog = 1
  17. max_binlog_size=1024M
  18. max_binlog_cache_size=4096M
  19. binlog_stmt_cache_size=1M
  20. binlog_checksum = none
  21. binlog_format = mixed
  22. binlog_cache_size=4M
  23. expire_logs_days=30
  24. relay_log_purge=0
  25. skip-name-resolve
  26. # InnoDBSetting
  27. innodb_buffer_pool_size=16384M
  28. innodb_log_file_size=512M
  29. innodb_log_buffer_size=16M
  30. innodb_log_files_in_group=3
  31. innodb_flush_log_at_trx_commit=1
  32. innodb_stats_on_metadata=OFF
  33. innodb_flush_method=O_DIRECT
  34. table_open_cache=4096
  35. sort_buffer_size=2M
  36. # 允许最大连接数
  37. max_connections=800
  38. # 服务端使用的字符集默认为8比特编码的latin1字符集
  39. character-set-server=utf8
  40. # 创建新表时将使用的默认存储引擎
  41. default-storage-engine=INNODB
  42. lower_case_table_names=1
  43. max_allowed_packet=16M
  44. EOF

 三台云主机都下载mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

  1. cd /usr/local/
  2. wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
  3. tar -zxf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
  4. mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql/
  5. chown -R mysql:mysql mysql/
  6. cd /home/mysql && mkdir data
  7. chown -R mysql:mysql /home/mysql
  8. cd /usr/local/mysql
  9. bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data/
  10. cp ./support-files/mysql.server /etc/init.d/mysqld
  11. chmod a+x /etc/init.d/mysqld
  12. netstat -lntp |grep mysqld
  13. cd /etc/profile.d/
  14. vi mysql.sh
  15. export PATH=$PATH:/usr/local/mysql/bin
  16. source mysql.sh

三台云主机DB01,DB02,DB03分别执行 

/etc/init.d/mysqld start

获取root的初始密码:

cat /root/.mysql_secret

 登录修改root密码(内网一般%变为192.168.1.%):

  1. mysql -uroot -p
  2. mysql>>ALTER USER 'root'@'localhost' identified by 'TANGshupei1!';
  3. mysql>>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'TANGshupei1!' WITH GRANT OPTION;
  4. mysql>>FLUSH PRIVILEGES;
  5. mysql>>exit
  6. killall mysqld
  7. /etc/init.d/mysqld restart
  8. #设置开机自启动
  9. chkconfig --add mysqld
  10. chkconfig --level 35 mysqld on
  11. chkconfig --list mysqld
  12. service mysqld status

 主从配置
登录DB01主库
mysql -uroot -p

创建replication用户并设置密码为replication

重启DB01数据库

 记录下来:File文件名和Position的位置配置DB02从库

  1. >>>CREATE USER replication IDENTIFIED BY 'replication';
  2. >>>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication' ;
  3. >>>exit
  4. 重启DB01数据库
  5. service mysqld restart
  6. #登录数据库查询master_log_file和master_log_position
  7. mysql -uroot -p
  8. password:********
  9. mysql> show master status;
  10. +------------------+----------+--------------+------------------+-------------------+
  11. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  12. +------------------+----------+--------------+------------------+-------------------+
  13. | mysql-bin.000004 | 770 | | mysql | |
  14. +------------------+----------+--------------+------------------+-------------------+
  15. 1 row in set (0.00 sec)

重启MySQL
service mysqld restart

登录DB02数据库

  1. >>>CHANGE MASTER TO master_host='10.100.10.190',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000004',master_log_pos=1631;
  2. >>>START SLAVE;
  3. >>>show slave status;

 登录DB03数据库:将最新获取的DB01日志文件和id替换

  1. >>>CHANGE MASTER TO master_host='10.100.10.190',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000002',master_log_pos=2247;
  2. >>>START SLAVE;
  3. >>>SHOW SLAVE STATUS;

从同步只能只读,不能增删改,不小心操作了1,回滚恢复2,从新连接同步

  1. mysql> show slave status;
  2. Slave_IO_State: Waiting for master to send event
  3. Master_Host: 192.168.2.222 //主服务器地址
  4. Master_User: mysync //授权帐户名,尽量避免使用root
  5. Master_Port: 3306 //数据库端口,部分版本没有此行
  6. Connect_Retry: 60
  7. Master_Log_File: mysql-bin.000004
  8. Read_Master_Log_Pos: 600 //#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
  9. Relay_Log_File: ddte-relay-bin.000003
  10. Relay_Log_Pos: 251
  11. Relay_Master_Log_File: mysql-bin.000004
  12. Slave_IO_Running: Yes //此状态必须YES
  13. Slave_SQL_Running: Yes //此状态必须YES

1.一般是事务回滚造成的:
解决办法:
 

  1. mysql>  stop slave;
  2. mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
  3. mysql> start slave ;

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/一键难忘520/article/detail/869473
推荐阅读
相关标签
  

闽ICP备14008679号