Setting up MySQL two master and one slave environment (5.7.24)

itread01 2020-11-06 20:33:58
setting mysql master slave environment


## Build instructions * Two masters and one subordinate , In essence , It's just a machine master-a and master-b They are masters and slaves of each other ( Hot standby ), And then through **keepalived** High availability configuration , So that at the same time there will be only one external service , Implement single write mechanism , Another machine slave As master-a The slave exists ( Cold preparation ). * Slave slave Can't directly virtual IP(VIP) As a host : In normal execution ,slave Bound to a host master-a, Once tied , stay master-a When it's not available , Can't automatically switch to master-b, Because you need to think about Binary log file name (master_log_file) And location (master_log_pos) stay master-a and master-b It's not consistent , At this time, only in master-a When it is available again ,master-a From master-b After synchronizing the data ,slave The data will eventually come into contact with master-a bring into correspondence with . ## Environmental preparation 1. Three centos7 Server for , Server ip For 192.168.86.124(master-a)、192.168.86.125(master-b)、192.168.86.126(slave) 2. A virtual IP(VIP):192.168.86.250 3. [mysql-5.7.24-linux-glibc2.12-x86_64 Installation package ](https://downloads.mysql.com/archives/community/) ## Database installation 1. Will mysql The installation package is uploaded to three servers 2. Unzip the installation package ``` tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz ``` 3. establish mysql Installation path , Then move the folder you just extracted and rename it ``` mkdir /data # Set up the installation path mv mysql-5.7.24-linux-glibc2.12-x86_64 /data/mysql # Move files and rename ``` 4. establish mysql Users and groups 、 establish mysql Implement the required folder and authorization ``` groupadd mysql # Create user groups useradd -r -g mysql mysql # Create users cd /data/mysql # Switch to the installation directory mkdir data # Create a data storage directory mkdir mysql-log # Create a log directory mkdir mysql-log/err-log mkdir mysql-log/slow-log mkdir mysql-log/relay-log mkdir mysql-log/bin-log chown -R root:mysql . # Put the current directory and subdirectories , The owner changed to mysql, Change the group to mysql chown -R mysql:mysql data chown -R mysql:mysql mysql-log ``` 5. Set user operating system resource limits ``` vi /etc/security/limits.conf # Add the following to the file mysql soft nproc 2047 mysql hard nproc 16384 mysql soft nofile 1024 mysql hard nofile 65536 ``` 6. Set up links and services ``` cp /data/mysql/support-files/mysql.server /etc/init.d/mysql # have access to service mysql start ln -s /data/mysql/bin/mysql /usr/bin # Any path can be used mysql command ``` 7. To configure /etc/my.cnf ``` vi /etc/my.cnf ``` * master-a ``` [mysqld] port = 3306 # Port basedir = /data/mysql #mysql Installation path datadir = /data/mysql/data/ #mysql Data storage path # Log settings log-error = /data/mysql/mysql-log/err-log/db-err.log # Error log path slow-query-log-file = /data/mysql/mysql-log/slow-log/db-slow.log # slow SQL Log path relay-log=/data/mysql/mysql-log/relay-log/relay-log long-query-time = 20 # What is slow sql, In seconds # Turn on binlog Sync server_id = 0001 # In a cluster MySQL Server ID, Global uniqueness log-bin = /data/mysql/mysql-log/bin-log/db-binlog # Turn on Binlog And write down the location of the log max-binlog-cache_size = 64M #binlog Maximum use of cache Memory size of max-binlog-size = 1G #binlog Every time the log reaches the set size , Will use the new binlog The Journal expire_logs_days = 15 # Keep only the recent 15 Days of bin The Journal binlog-format = mixed # Hybrid mode replication innodb_flush_log_at_trx_commit = 1 # and sync_binlog control MySQL Disk write policy and data security sync-binlog = 1 # Controlling the database binlog Brush to disk ## Master master synchronization configuration replicate-do-db=db_test # Need to be from master library -2 Synchronized databases 1 replicate-do-db=db_dev # Need to be from master library -2 Synchronized databases 2 auto-increment-increment=2 auto-increment-offset=1 log-slave-updates=1 # Performance tuning configuration innodb_buffer_pool_size = 24576M max_connections = 5000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 64M join_buffer_size = 64M sort_buffer_size = 2M read_rnd_buffer_size = 16M #SQL Pattern sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` * master-b ``` [mysqld] port = 3306 basedir = /data/mysql datadir = /data/mysql/data # Log settings slow-query-log-file=/data/mysql/mysql-log/slow-log/db-slow.log log-error=/data/mysql/mysql-log/err-log/db-err.log relay-log=/data/mysql/mysql-log/relay-log/relay-log long-query-time = 20 # What is slow sql, In seconds # Turn on binlog Sync server_id = 0002 # In a cluster MySQL Server ID, Global uniqueness log-bin = /data/mysql/mysql-log/bin-log/db-binlog # Turn on Binlog And write down the location of the log max-binlog-cache_size = 64M #binlog Maximum use of cache Memory size of max-binlog-size = 1G #binlog Every time the log reaches the set size , Will use the new binlog The Journal expire_logs_days = 15 # Keep only the recent 15 Day's diary binlog-format = mixed # Hybrid mode replication innodb_flush_log_at_trx_commit = 1 # and sync_binlog control MySQL Disk write policy and data security sync-binlog = 1 # Controlling the database binlog Brush to disk # Master master settings replicate-do-db=db_test # Need to be from master library -1 Synchronized databases 1 replicate-do-db=db_dev # Need to be from master library -1 Synchronized databases 2 auto-increment-increment=2 auto-increment-offset=2 log-slave-updates=1 # Performance tuning settings innodb_buffer_pool_size = 24576M max_connections = 5000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 64M join_buffer_size = 64M sort_buffer_size = 2M read_rnd_buffer_size = 16M #SQL Pattern sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` * slave ``` [mysqld] port = 3306 basedir = /data/mysql datadir = /data/mysql/data # Log settings expire_logs_days = 15 long-query-time = 3 slow-query-log-file=/data/mysql/mysql-log/slow-log/db-slow.log log-error=/data/mysql/mysql-log/err-log/db-err.log # Master slave settings server_id = 0003 # Here you need to be unique in the cluster replicate-do-db=db_test # Databases that need to be synchronized from the master database 1 replicate-do-db=db_dev # Databases that need to be synchronized from the master database 2 read_only=1 # Read only settings # Performance tuning settings innodb_buffer_pool_size = 24576M max_connections = 5000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 64M join_buffer_size = 64M sort_buffer_size = 2M read_rnd_buffer_size = 16M #SQL Pattern sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` ## Database initialization 1. Initialize database ``` cd /data/mysql/bin/ ./mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize # initialization ``` 2. Check the password ( After initializing the database, a root The default password for the account ) ``` cat /data/mysql/mysql-log/err-log/db-err.log ``` 3. Start mysql ``` service mysql start ``` 4. Log in and modify root code ``` mysql -u root -p set password = password('root'); flush privileges; # If needed root Make a remote connection , Execute the following statement use mysql; update user set host = '%' where user = 'root'; flush privileges; ``` 5. Create users and empower * Host master-a、master-b ``` # Create a slave for and slave “ Communications ” Users of , Have synchronization permission grant replication slave on *.* to 'copy'@'%' identified by 'copy' flush privileges; # Create a user to access and manipulate specific libraries , Applications should not be allowed to use directly root Users . grant all privileges on db_test.* to "app_user"@'%' identified by "app_user"; flush privileges; ``` * Slave slave ``` # Create a user to access and manipulate specific libraries , Applications should not be allowed to use directly root Users . grant all privileges on db_test.* to "app_user"@'%' identified by "app_user"; flush privileges; ``` ## Master slave synchronous start 1. Host master-a 1. stay master-b Get binary log file name from (master_log_file) And location (master_log_pos) ``` show master status ``` ![image](https://images.cnblogs.com/cnblogs_com/shulipeng/1877135/o_2011060717111588349415(1).png) 2. stay master-a Execute the following command ``` stop slave; # Pause reset slave; # Reset change master to master_host="master-b Of IP", master_user="copy",master_password="copy",master_log_file="db-binlog.000002",master_log_pos=2532; # Set the user information synchronized with the host , Log file information start slave; # Start show slave status \G # View slave status , Mainly to see Slave_IO_Running Slave_SQL_Running Is it all yes ``` ![image](https://images.cnblogs.com/cnblogs_com/shulipeng/1877135/o_2011060717201588349665(1).png) 2. Host master-b、 Slave slave 1. stay master-a Get binary log file name from (master_log_file) And location (master_log_pos) ``` show master status ``` 2. stay master-b and slave Execute the following command ``` stop slave; # Pause reset slave; # Reset change master to master_host="master-a Of IP", master_user="copy",master_password="copy",master_log_file="db-binlog.000002",master_log_pos=2532; # Set the user information synchronized with the host , Log file information start slave; # Start show slave status \G # View slave status , Mainly to see Slave_IO_Running Slave_SQL_Running Is it all yes ``` ## keepalived Installation start up (master-a、master-b) 1. keepalived Install ``` yum install -y keepalived ``` 2. keepalived To configure ``` vi /etc/keepalived/keepalived.conf ``` The content of the file is : ``` global_defs { router_id LVS_DEVEL } vrrp_sync_group VG_1 { group { VI_1 } } vrrp_instance VI_1 { state BACKUP # Both machines have this value , Don't set it to MASTER, Use the following priority To control interface ens33 # Through ifconfig Look at virtual_router_id 51 priority 100 # Priorities , The other machine is set to 90 advert_int 1 nopreempt # Another low priority machine does not set this argument ! authentication { auth_type root auth_pass root } virtual_ipaddress { 192.168.86.250 # This is VIP } } virtual_server 192.168.86.250 3306 { # This is VIP delay_loop 2 lb_algo rr lb_kind DR persistence_timeout 50 protocol TCP real_server 192.168.86.124 3306 { # This is the machine IP Address weight 3 notify_down /data/mysql/bin/mysql.sh TCP_CHECK { connect_timeout 5 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } } ``` 3. Create a closure keepalived The command code of , Used in mysql When you can't access , Shut down keepalived To release VIP ``` vi /data/mysql/bin/mysql.sh ``` The content of the file is : ``` #!/bin/sh pkill keepalived ``` Make the file executable : ``` chmod +x /data/mysql/bin/mysql.sh ``` 4. keepalived Start ``` systemctl start keepalived ``` 5. keepalived Other related commands ``` systemctl enable keepalived # Power on and self start systemctl start keepalived # Start systemctl stop keepalived # Pause systemctl restart keepalived # Restart systemctl status keepalived # Check the status tail -f /var/log/messages ``` ## Test 1. First , All the machines 、 The software runs normally * Through various IP and VIP The connection database view can connect to the database normally ![image](https://images.cnblogs.com/cnblogs_com/shulipeng/1877135/o_201106071105110614430417_01604632406(1).png?a=1604647194590) * master-a Got vip ``` ip addr ``` ![image](https://images.cnblogs.com/cnblogs_com/shulipeng/1877135/o_2011060717281026616-20201106105155900-1945251753.png?a=1604647194590) 2. Through VIP Connect to the database , And then set up a db_test Library and tb_user surface * master-a、master-b、slave There are established libraries and tables 3. Connect master-a The database of , And add a piece of information * master-b、slave There are new information 4. Connect master-b The database of , And add a piece of information * master-a、slave There are new information 5. Shut down master-a Of mysql ``` service mysql stop # Shut down mysql systemctl status keepalived # Look at keepalived Status ``` * master-a Of keepalived It's turned off automatically , as a result of mysql Closing triggers keepalived Configured notify_down Instruction code * master-b Got vip 6. Through VIP Connect to the database , And insert a piece of information * master-a( Can't connect ) * master-b ( There are new data ) * slave( There is no new data , Because master-a Hang up ) 7. Restart master-a Of mysql and keepalived * vip Still in master-b , Because master-a keepalived Non preemptive mode is configured **nopreempt**, Therefore, if the host machine troubleshooting needs to be the host of keepalived Restart , Then restart the slave keepalived, The host needs to get VIP * master-a、master-b Synchronize the newly added data 8. Shut down master-b、slave Of mysql, Through master-a Add a piece of data , Then restart master-b、slave Of mysql, as well as master-b Of keepalived * master-b There are new data * slave There are new data * vip Still in master-a ## often
版权声明
本文为[itread01]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云