Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL High Availability plan several options     - Use PDFBox parse PDF file (Linux)

- Repair Maven project developed default Maven Plugin folder (Linux)

- Linux system server network security management tips (Linux)

- Use Redis as time-series database: why and how (Database)

- Protobuf compiled and used on the Ubuntu 14.04 (Programming)

- How to achieve SSH without password login (Server)

- Analysis of Java reflection mechanism (Programming)

- Ubuntu 14.04 build Android 5.1 development environment and compiler (Linux)

- CentOS7 install JDK (Linux)

- Fedora 23 How to install LAMP server (Server)

- JBPM6 Tutorial - Fast Fun JBPM table (Linux)

- Monitoring network traffic with Iptraf in Linux environment (Linux)

- Unity Greeter Badges: the lost session icon back to the login screen Ubuntu (Linux)

- The sublime into IDE (Linux)

- Differences Shell scripting languages and compiled languages (Programming)

- Three kinds of implementation model of the Linux thread history (Programming)

- CentOS7 minimized installation can not find the 'ifconfig' command - Repair Tips (Linux)

- PL / SQL how to make the program every few seconds to insert a data (Database)

- Json data with double backslashes to a single backslash Json data processing (Programming)

- Android custom controls create the simplest skid menu in the history (Programming)

 
         
  MySQL High Availability plan several options
     
  Add Date : 2018-11-21      
         
         
         
  Mysql-mmm is mysql master-master replication manager, the functions are:
High Availability (similar keepalived of vip floating technique)
The same time only one database write operation, to ensure consistency of the database.
Enhance slave to master, the continuation of dual master architecture
 
First to build a good database master master, master-slave architecture: Similar :( not described)
In each database to monitor the current situation to monitor synchronous machines:
Grant replivation client to 'user @' ip 'identified by' password 'Create a mmm-monitor accounts, mainly used for monitoring and synchronization
Grant super, replication client, process on *. * To 'user' @ 'ip' identified by 'password'
Create a mmm-agent accounts for a variety of client operations

The use of the epel way need to /etc/yum.repos.d/epel.repo in mirrorlist replaced baseurl
 
=================================================

Mmm_common.conf configuration files: All configuration node are the same, including monitoring terminal

active_master_role writer
 

    cluster_interface eth0
    pid_path /var/run/mysql-mmm/mmm_agentd.pid
    bin_path / usr / libexec / mysql-mmm /
    replication_user ccd // synchronization account
    replication_password ccd
    agent_user ccd // bigger authority
    agent_password ccd


     ip 172.20.138.212
     mode master
     peer db2


  ip 172.20.138.209
  mode master
  peer db1


    hostsdb1, db2
    ips 172.20.138.100
    mode exclusive // ​​exclusive single write

 

    Hosts b1, db2
    ips 172.20.138.250 // can have multiple ip, each database can have a separate vip
    mode balanced // read as load balancing

==============================================================
Modify file mmm_agent.conf different db value
 
Finally, configure the look mmm_mon.conf
Where major changes are:
Ping_ips to be true ip monitor host,
Monitor accounts to modify the database conferred
Monitor_assword password
 
Start the service in /etc/init.d/, enter the command in the monitoring server:
Mmm_control checks Check all database synchronization and operational conditions
Mmm_control show View vip bind situation similar ipvsadm
 
Mysql-mmm not suitable for high load mysql master-slave
Can be verified vip jump, but found reading ip also write data function. . . . If coupled with separate read and write, then you can achieve read vip does not have write capabilities
 
Mysql-proxy load balancing and separate read and write
The current version is mysql-proxy 0.8.5aplha
 
Before you install mysql-proxy will need to install lua
To the official website to download the source files lua
yum -y install readline-devel ncurses-devel
Install readline-devel and install ncurses-devel otherwise occur readline find no problem
tar zxvf luaxxxxx
make linux
make install
This time can be input into the compilation mode lua lua indicates the installation was successful
 
Install the required dependencies
yum install gcc * gcc-c ++ * autoconf * automake * zlib * libxml * ncurses-devel * libmcrypt * libtool * flex * pkgconfig *
 
Mysql-proxy to the official website to download the latest source file mysql-proxy
After the installation is very simple to use decompression
Add the mysql-proxy to the corresponding environment variable
export PATH = $ PATH: / usr / local / mysql-proxy
source / etc / profile
 
 
Modify mysql-proxy configuration files
Commonly used command parameters are as follows:
--daemon start in daemon mode
--admin-address =: 4401 is the default port 4401
See all -help command
-default-file can be configured startup configuration file
--proxy-backend-addresses = $ host: $ port multiple separated by commas
--proxy-address =: 3307
--proxy-backend-addresses =: 3306 mysql host port
--proxy-read-only-backend-address = xxx13306 read only mysql host port
- Proxy-read-only-backend-address = xxx23306 can specify multiple read-only mysql host port
--proxy-lua-script = / usr / local / xxx / rw-splitting.lua lua scripts to use the system only with the realization of separate read and write functions
 
Use the system comes with two parameters need to modify the script rw.split.lua
min_idle_connections = 1
max_idle_connections = 3
 
Startup script:
#! / Bin / bash
mode = $ 1
if [-z "$ mode"]; then
  mode = "start"
fi
case $ mode in
'Start')
  mysql-proxy --daemon --proxy-address =: 3307 --proxy-backend-addresses = 172.20.138.209: 3306; 172.20.138.212: 3306 --proxy-read-only-backend-addresses = 172.20.138.212: 3306 --proxy-lua-script = / usr / local / mysql-proxy / share / doc / mysql-proxy / rw-splitting.lua
  ;;
'Stop')
  killall mysql-proxy
  ;;
esac
exit 0
 
Or use the configuration method to start
Vim /etc/mysql-proxy.cnf
admin-username = ccd
admin-password = ccd
daemon = true
keepalive = true
proxy-backend-addresses = 172.20.138.209: 3306; 172.20.138.212: 3306
proxy-read-only-backend-addresses = 172.20.138.212: 3306
proxy-lua-script = / usr / local / proxy-mysql / share / doc / mysql-proxy / rw-splitting.lua
admin-lua-script = / usr / local / proxy-mysql / share / doc / mysql-proxy / admin-sql.lua
log-file = /usr/local/proxy-mysql/cn.log
log-level = debug
Modify the corresponding scripts to:
Mysql-proxy -defaults-file = / etc / mysql-proxy.cnf
 
For the test database can use the tool to test sysbench
 
Keepalived use mysql to achieve the main active / active high availability from
Keepalived.conf configuration file:
Vrrp_instatance HA_1 {
State BACKUP # configured as a slave, it can also be a master
Interface eth0
Virtual_router_id 80 // master-slave configuration requires the same virtual routing identifier
Different configurable priority Priority 100 #
Advert_int 1 # Check the synchronization between the primary and secondary cycle configuration
Authenication {
Auth_type PASS
Auth_pass 1111
}
Virtual_ipaddress {
    vip / 24 dev eth0
}
}
Virtual_server xxxxxx 3306 {
Delay_loop 2
lb_algo wrr // lvs WRR algorithm
lb_kind DR // lvs model for DR mode
protocol TCP
reall_server xxxx 3306 {
weight 1
TCP_CHECK {
Connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
 
Real server xxxx 3306 {
  Ditto
}
}
 
DR's real serverloop port need to bind vip address script:
#! / Bin / bash
SNS_VIP = 172.138.20.244
./etc/rc.d/init.d/functions
case "$ 1" in
start)
      ifconfig lo: 0 $ SNS_VIP netmask255.255.255.255 broadcast $ SNS_VIP
 
      / Sbin / route add -host $ SNS_VIP dev lo: 0
 
      echo "1"> / proc / sys / net / ipv4 / conf / lo / arp_ignore
 
      echo "2"> / proc / sys / net / ipv4 / conf / lo / arp_announce
 
      echo "1"> / proc / sys / net / ipv4 / conf / all / arp_ignore
 
      echo "2"> / proc / sys / net / ipv4 / conf / all / arp_announce
 
      sysctl -p> / dev / null 2> & 1
 
      echo "RealServer Start OK"
 
 
 
      ;;
 
stop)
 
      ifconfig lo: 0 down
 
      route del $ SNS_VIP> / dev / null2> & 1
 
      echo "0"> / proc / sys / net / ipv4 / conf / lo / arp_ignore
 
      echo "0"> / proc / sys / net / ipv4 / conf / lo / arp_announce
 
      echo "0"> / proc / sys / net / ipv4 / conf / all / arp_ignore
 
      echo "0"> / proc / sys / net / ipv4 / conf / all / arp_announce
 
      echo "RealServer Stoped"
 
      ;;
 
*)
 
      echo "Usage: $ 0 {start | stop}"
 
      exit 1
 
esac
 
exit 0
 
Authorized to connect to the remote host can log in via vip vip
Grant allprivileges to on *. * To xxxx identifiedby xxxxx
    Flushprivileges
You can achieve high availability mysql database
 
There are two ways to use keepalived one for keepalived plus scripts vip switch, the other is keepalived + lvs way to achieve high availability database.
     
         
         
         
  More:      
 
- How to modify the Emacs Major Mode Shortcuts (Linux)
- Linux 4.0+ kernel support for hardware switching module (HW Switch Offload) (Linux)
- CentOS7 build GlusterFS (Linux)
- Copy U disk files to the Linux system on a virtual machine (Linux)
- RedHat command line and graphical interface switching (Linux)
- Debian installation (Linux)
- Graphic Git (Linux)
- File compression and packaging commands under Linux (Linux)
- MongoDB in bulk timestamp change the date format (Database)
- Analysis of MySQL Dockerfile 5.6 (Database)
- Android use canvas board painting (Programming)
- CentOS7 installation GAMIT and GMT (Linux)
- Linux - use chroot command (Linux)
- Linux script commands - terminal recorder (Linux)
- Upgrading KDE Plasma 5.3 in Ubuntu 15.04 (Linux)
- How to update the Linux kernel to improve system performance (Linux)
- [Errno 4] IOError: [Errno ftp error] with yum appears as a workaround (Linux)
- Security Knowledge: How to hide a backdoor PHP file tips (Linux)
- Installation of JDK and Tomcat under Linux (CentOS) (Linux)
- Linux Getting Started tutorial: XWindow what (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.