MySQL双主互备+Keepalived高可用架构实现案例

yum  clean all
 
yum -y update
 
yum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses  cmake libaio libaio-devel  boost
 
yum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip
 
yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-dev
 
yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt
 
yum -y install gcc automake autoconf libtool openssl-devel
 
yum -y install perl-devel perl-ExtUtils-Embed
 
yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison
 
yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel
 
yum -y install ntpdate rsync svn  patch  iptables iptables-services
 
yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel
 
yum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make
 
yum -y groupinstall "Server Platform Development" "Development tools"
 
yum -y groupinstall "Development tools" 

rm -rf /etc/my.cnf  
 
mkdir -p /opt/mysql
 
cd /opt/mysql/
 
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
 
tar -zxf  mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
 
cd /usr/local/
 
ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64  mysql

mkdir  -p /data/mysql/mysql_8306/{data,logs,tmp}

groupadd mysql
 
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

cat >/data/mysql/mysql_8306/my_8306.cnf <<EOF
 
#my.cnf
 
[client]
 
port            = 8306
 
socket          = /data/mysql/mysql_8306/tmp/mysql_8306.sock
 
 
 
[mysql]
 
#prompt="\u@\h:\p  \R:\m:\s [\d]>"
 
#tee=/data/mysql/mysql_8306/data/query.log
 
#prompt="\u@\h:\p  \R:\m:\s [\d]>"
 
prompt = "[\u@\h][\d]>\_"  
 
connect_timeout = 5
 
no-auto-rehash
 
 
 
[mysqld]
 
#misc
 
user = mysql
 
basedir = /usr/local/mysql
 
datadir = /data/mysql/mysql_8306/data
 
port = 8306
 
socket = /data/mysql/mysql_8306/tmp/mysql_8306.sock
 
 
 
#timeout
 
interactive_timeout = 300
 
wait_timeout = 300
 
 
 
#character set
 
character-set-server = utf8
 
 
 
open_files_limit = 65535
 
max_connections = 100
 
max_connect_errors = 100000
 
 
 
skip-name-resolve = 1
 
#logs
 
log-output=file
 
slow_query_log = 1
 
slow_query_log_file = /data/mysql/mysql_8306/logs/slow.log
 
log-error = /data/mysql/mysql_8306/logs/error.log
 
log_error_verbosity = 3
 
pid-file = mysql.pid
 
long_query_time = 1
 
#log-slow-admin-statements = 1
 
#log-queries-not-using-indexes = 1
 
log-slow-slave-statements = 1
 
 
 
#tmp
 
tmpdir=/data/mysql/mysql_8306/tmp
 
event_scheduler = 1
 
performance_schema = on
 
max_allowed_packet = 32M
 
character_set_server = utf8mb4
 
#character_set_server = utf8
 
default-time-zone = system
 
default-storage-engine = InnoDB
 
#bind_address = 172.16.151.248
 
explicit_defaults_for_timestamp = 1
 
 
 
 
 
#binlog
 
binlog_format = row
 
server-id = 1818306
 
log-bin = /data/mysql/mysql_8306/logs/mysql-bin
 
log-bin-index = /data/mysql/mysql_8306/logs/mysql-bin.index
 
binlog_cache_size = 4M
 
max_binlog_size = 1G
 
max_binlog_cache_size = 2G
 
sync_binlog = 0
 
expire_logs_days = 90
 
#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
 
 
 
#relay log
 
skip_slave_start = 1
 
max_relay_log_size = 1G
 
relay_log_purge = 1
 
relay_log_recovery = 1
 
log_slave_updates
 
#slave-skip-errors=1032,1053,1062
 
 
 
explicit_defaults_for_timestamp=1
 
#buffers & cache
 
table_open_cache = 2048
 
table_definition_cache = 2048
 
table_open_cache = 2048
 
max_heap_table_size = 96M
 
sort_buffer_size = 2M
 
join_buffer_size = 2M
 
thread_cache_size = 256
 
query_cache_size = 0
 
query_cache_type = 0
 
query_cache_limit = 256K
 
query_cache_min_res_unit = 512
 
thread_stack = 192K
 
tmp_table_size = 96M
 
key_buffer_size = 8M
 
read_buffer_size = 2M
 
read_rnd_buffer_size = 16M
 
bulk_insert_buffer_size = 32M
 
 
 
#myisam
 
myisam_sort_buffer_size = 128M
 
myisam_max_sort_file_size = 10G
 
myisam_repair_threads = 1
 
 
 
#innodb
 
innodb_buffer_pool_size = 10G
 
innodb_buffer_pool_instances = 1
 
innodb_data_file_path = ibdata1:1G:autoextend
 
innodb_flush_log_at_trx_commit = 2
 
innodb_log_buffer_size = 64M
 
innodb_log_file_size = 500M
 
innodb_log_files_in_group = 3
 
innodb_max_dirty_pages_pct = 50
 
innodb_file_per_table = 1
 
innodb_rollback_on_timeout
 
innodb_status_file = 1
 
innodb_io_capacity = 2000
 
transaction_isolation = READ-COMMITTED
 
innodb_flush_method = O_DIRECT
 
gtid_mode = ON
 
enforce_gtid_consistency = ON
 
master_info_repository = TABLE
 
relay-log-info-repository = TABLE
 
binlog_checksum = NONE
 
log_slave_updates = ON
 
 
 
# Two-Master configure
 
#server-1
 
auto-increment-offset = 1
 
auto-increment-increment = 2
 
 
 
#server-2                          
 
#auto-increment-offset = 2
 
#auto-increment-increment = 2
 
 
 
# semi sync replication settings #
 
plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径
 
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
 
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径
 
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
 
 
 
loose_rpl_semi_sync_master_enabled = on     
 
loose_rpl_semi_sync_master_timeout = 5000      
 
loose_rpl_semi_sync_master_trace_level = 32   
 
loose_rpl_semi_sync_master_wait_no_slave = on
 
loose_rpl_semi_sync_slave_enabled = on      
 
loose_rpl_semi_sync_slave_trace_level = 32
 
loose_rpl_semi_sync_master_enabled = 1
 
loose_rpl_semi_sync_slave_enabled = 1
 
loose_rpl_semi_sync_master_timeout = 5000
 
loose_rpl_semi_sync_master_wait_for_slave_count=1
 
loose_rpl_semi_sync_master_wait_point=AFTER_SYNC
 
 
 
slave_preserve_commit_order = 1
 
slave_transaction_retries = 128
 
log_timestamps = system
 
show_compatibility_56 = on
 
slave_parallel_workers = 16
 
slave_parallel_type = LOGICAL_CLOCK
 
 
 
loose_innodb_numa_interleave = 1
 
innodb_buffer_pool_dump_pct = 40
 
innodb_page_cleaners = 16
 
innodb_undo_log_truncate = 1
 
innodb_max_undo_log_size = 2G
 
innodb_purge_rseg_truncate_frequency = 128
 
#transaction_write_set_extraction = MURMUR32
 
 
 
 
 
# group replication
 
##log-bin = mysql
 
##server-id = 618306
 
##gtid_mode = ON
 
##enforce_gtid_consistency = ON
 
##master_info_repository = TABLE
 
##relay-log-info-repository = TABLE
 
##binlog_checksum = NONE
 
##log_slave_updates = ON
 
##binlog_format = row
 
##transaction_write_set_extraction=XXHASH64
 
##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'
 
##loose-group_replication_start_on_boot = off
 
##loose-group_replication_local_address= "10.125.141.62:28306"  # 不能超过 5 位数字
 
##loose-group_replication_group_seeds= "10.125.141.62:28306,10.125.141.62:23307,10.125.141.62:23308"  # 不能超过 5 位数字
 
##loose-group_replication_bootstrap_group= off
 
# loose-group_replication_single_primary_mode=FALSE   ### 本次搭建的是 mutil_mode
 
# loose-group_replication_enforce_update_everywhere_checks= TRUE
 
 
 
[mysqld_safe]
 
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
 
nice=-19
 
open-files-limit=65535
 
EOF

chown -R mysql.mysql /data/mysql/

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --initialize-insecure &

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &

/usr/local/mysql/bin/mysql  -uroot -p -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock

/usr/local/mysql/bin/mysql  -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock

set sql_log_bin = 0;
 
create user 'rpl_user'@'%';
 
grant replication slave on *.* to 'rpl_user'@'%' identified by 'rpl_user2017';
 
update mysql.user set authentication_string=password('root2017') where user='root';
 
flush privileges;
 
set sql_log_bin = 1;
 
reset  master ; reset slave all; 

CHANGE MASTER TO MASTER_HOST='172.16.210.181',MASTER_USER='rpl_user',
 
MASTER_PASSWORD='rpl_user2017',MASTER_PORT=8306,
 
MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1; 
 
start slave;
 
show slave status\G; 

[root@my1 local]# /usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=8306_localhost_login \
 
> --user=root --port=8306  --password --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock
 
Enter password:
 
[root@my1 local]#
 
 
# 查看
 
/usr/local/mysql/bin/mysql_config_editor print --all
 
 
 
[root@my1 local]# alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'

[root@my1 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

[root@my1 ~]# cat  >>/root/.bashrc <<EOF
 
alias mysql.8306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &'
 
alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'
 
alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'
 
EOF
 
 
[root@my1 ~]# source /root/.bash_profile

[root@my1 ~]# cat /root/all_database.sh
 
#!/bin/bash
 
/usr/local/mysql/bin/mysqldump --login-path=8306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384  --master-data=2 --single-transaction --all-databases --quick  | gzip >/root/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz
 
[root@my1 ~]# 

yum  clean all
 
yum -y update
 
yum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses  cmake libaio libaio-devel  boost
 
yum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip
 
yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-dev
 
yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt
 
yum -y install gcc automake autoconf libtool openssl-devel
 
yum -y install perl-devel perl-ExtUtils-Embed
 
yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison
 
yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel
 
yum -y install ntpdate rsync svn  patch  iptables iptables-services
 
yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel
 
yum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make
 
yum -y groupinstall "Server Platform Development" "Development tools"
 
yum -y groupinstall "Development tools"

rm -rf /etc/my.cnf  
 
mkdir -p /opt/mysql
 
cd /opt/mysql/
 
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
 
tar -zxf  mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
 
cd /usr/local/
 
ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64  mysql

mkdir  -p /data/mysql/mysql_8306/{data,logs,tmp} 

groupadd mysql
 
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

cat >/data/mysql/mysql_8306/my_8306.cnf <<EOF
 
#my.cnf
 
[client]
 
port            = 8306
 
socket          = /data/mysql/mysql_8306/tmp/mysql_8306.sock
 
 
 
[mysql]
 
#prompt="\u@\h:\p  \R:\m:\s [\d]>"
 
#tee=/data/mysql/mysql_8306/data/query.log
 
#prompt="\u@\h:\p  \R:\m:\s [\d]>"
 
prompt = "[\u@\h][\d]>\_"  
 
connect_timeout = 5
 
no-auto-rehash
 
 
 
[mysqld]
 
#misc
 
user = mysql
 
basedir = /usr/local/mysql
 
datadir = /data/mysql/mysql_8306/data
 
port = 8306
 
socket = /data/mysql/mysql_8306/tmp/mysql_8306.sock
 
 
 
#timeout
 
interactive_timeout = 300
 
wait_timeout = 300
 
 
 
#character set
 
character-set-server = utf8
 
 
 
open_files_limit = 65535
 
max_connections = 100
 
max_connect_errors = 100000
 
 
 
skip-name-resolve = 1
 
#logs
 
log-output=file
 
slow_query_log = 1
 
slow_query_log_file = /data/mysql/mysql_8306/logs/slow.log
 
log-error = /data/mysql/mysql_8306/logs/error.log
 
log_error_verbosity = 3
 
pid-file = mysql.pid
 
long_query_time = 1
 
#log-slow-admin-statements = 1
 
#log-queries-not-using-indexes = 1
 
log-slow-slave-statements = 1
 
 
 
#tmp
 
tmpdir=/data/mysql/mysql_8306/tmp
 
event_scheduler = 1
 
performance_schema = on
 
max_allowed_packet = 32M
 
character_set_server = utf8mb4
 
#character_set_server = utf8
 
default-time-zone = system
 
default-storage-engine = InnoDB
 
#bind_address = 172.16.151.248
 
explicit_defaults_for_timestamp = 1
 
 
 
 
 
#binlog
 
binlog_format = row
 
server-id = 1818306
 
log-bin = /data/mysql/mysql_8306/logs/mysql-bin
 
log-bin-index = /data/mysql/mysql_8306/logs/mysql-bin.index
 
binlog_cache_size = 4M
 
max_binlog_size = 1G
 
max_binlog_cache_size = 2G
 
sync_binlog = 0
 
expire_logs_days = 90
 
#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
 
 
 
 
 
#relay log
 
skip_slave_start = 1
 
max_relay_log_size = 1G
 
relay_log_purge = 1
 
relay_log_recovery = 1
 
log_slave_updates
 
#slave-skip-errors=1032,1053,1062
 
 
 
explicit_defaults_for_timestamp=1
 
#buffers & cache
 
table_open_cache = 2048
 
table_definition_cache = 2048
 
table_open_cache = 2048
 
max_heap_table_size = 96M
 
sort_buffer_size = 2M
 
join_buffer_size = 2M
 
thread_cache_size = 256
 
query_cache_size = 0
 
query_cache_type = 0
 
query_cache_limit = 256K
 
query_cache_min_res_unit = 512
 
thread_stack = 192K
 
tmp_table_size = 96M
 
key_buffer_size = 8M
 
read_buffer_size = 2M
 
read_rnd_buffer_size = 16M
 
bulk_insert_buffer_size = 32M
 
 
 
#myisam
 
myisam_sort_buffer_size = 128M
 
myisam_max_sort_file_size = 10G
 
myisam_repair_threads = 1
 
 
 
#innodb
 
innodb_buffer_pool_size = 10G
 
innodb_buffer_pool_instances = 1
 
innodb_data_file_path = ibdata1:1G:autoextend
 
innodb_flush_log_at_trx_commit = 2
 
innodb_log_buffer_size = 64M
 
innodb_log_file_size = 500M
 
innodb_log_files_in_group = 3
 
innodb_max_dirty_pages_pct = 50
 
innodb_file_per_table = 1
 
innodb_rollback_on_timeout
 
innodb_status_file = 1
 
innodb_io_capacity = 2000
 
transaction_isolation = READ-COMMITTED
 
innodb_flush_method = O_DIRECT
 
gtid_mode = ON
 
enforce_gtid_consistency = ON
 
master_info_repository = TABLE
 
relay-log-info-repository = TABLE
 
binlog_checksum = NONE
 
log_slave_updates = ON
 
 
 
# Two-Master configure
 
#server-1
 
#auto-increment-offset = 1
 
#auto-increment-increment = 2
 
 
 
#server-2                          
 
auto-increment-offset = 2
 
auto-increment-increment = 2
 
 
 
# semi sync replication settings #
 
plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径
 
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
 
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径
 
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
 
 
 
loose_rpl_semi_sync_master_enabled = on     
 
loose_rpl_semi_sync_master_timeout = 5000      
 
loose_rpl_semi_sync_master_trace_level = 32   
 
loose_rpl_semi_sync_master_wait_no_slave = on
 
loose_rpl_semi_sync_slave_enabled = on      
 
loose_rpl_semi_sync_slave_trace_level = 32
 
loose_rpl_semi_sync_master_enabled = 1
 
loose_rpl_semi_sync_slave_enabled = 1
 
loose_rpl_semi_sync_master_timeout = 5000
 
loose_rpl_semi_sync_master_wait_for_slave_count=1
 
loose_rpl_semi_sync_master_wait_point=AFTER_SYNC
 
 
 
slave_preserve_commit_order = 1
 
slave_transaction_retries = 128
 
log_timestamps = system
 
show_compatibility_56 = on
 
slave_parallel_workers = 16
 
slave_parallel_type = LOGICAL_CLOCK
 
 
 
loose_innodb_numa_interleave = 1
 
innodb_buffer_pool_dump_pct = 40
 
innodb_page_cleaners = 16
 
innodb_undo_log_truncate = 1
 
innodb_max_undo_log_size = 2G
 
innodb_purge_rseg_truncate_frequency = 128
 
#transaction_write_set_extraction = MURMUR32
 
 
 
 
 
# group replication
 
##log-bin = mysql
 
##server-id = 618306
 
##gtid_mode = ON
 
##enforce_gtid_consistency = ON
 
##master_info_repository = TABLE
 
##relay-log-info-repository = TABLE
 
##binlog_checksum = NONE
 
##log_slave_updates = ON
 
##binlog_format = row
 
##transaction_write_set_extraction=XXHASH64
 
##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'
 
##loose-group_replication_start_on_boot = off
 
##loose-group_replication_local_address= "10.125.141.62:28306"  # 不能超过 5 位数字
 
##loose-group_replication_group_seeds= "10.125.141.62:28306,10.125.141.62:23307,10.125.141.62:23308"  # 不能超过 5 位数字
 
##loose-group_replication_bootstrap_group= off
 
# loose-group_replication_single_primary_mode=FALSE   ### 本次搭建的是 mutil_mode
 
# loose-group_replication_enforce_update_everywhere_checks= TRUE
 
 
 
[mysqld_safe]
 
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
 
nice=-19
 
open-files-limit=65535
 
EOF 

chown -R mysql.mysql /data/mysql/

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --initialize-insecure & 

# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &
 
 
#  推荐的启动方式
 
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &

/usr/local/mysql/bin/mysql  -uroot -p -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock
 
 
或者
 
/usr/local/mysql/bin/mysql  -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock

set sql_log_bin = 0;
 
create user 'rpl_user'@'%';
 
grant replication slave on *.* to 'rpl_user'@'%' identified by 'rpl_user2017';
 
update mysql.user set authentication_string=password('root2017') where user='root';
 
flush privileges;
 
set sql_log_bin = 1;
 
reset  master ; reset slave all; 

CHANGE MASTER TO MASTER_HOST='172.16.210.180',MASTER_USER='rpl_user',
 
MASTER_PASSWORD='rpl_user2017',MASTER_PORT=8306,
 
MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;
 
 
 
start slave;
 
show slave status\G;

[root@my2 local]# /usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=8306_localhost_login \
 
> --user=root --port=8306  --password --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock
 
Enter password:
 
[root@my2 local]#
 
# 查看
 
/usr/local/mysql/bin/mysql_config_editor print --all
 
 
 
[root@my2 local]# alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'

[root@my2 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

[root@my2 ~]# cat  >>/root/.bashrc <<EOF
 
alias mysql.8306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &'
 
alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'
 
alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'
 
EOF
 
[root@my2 ~]# source /root/.bash_profile 

[root@my2 ~]# cat /root/all_database.sh
 
#!/bin/bash
 
/usr/local/mysql/bin/mysqldump --login-path=8306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384  --master-data=2 --single-transaction --all-databases --quick  | gzip >/root/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz
 
[root@my2 ~]# 

[root@my1 ~]# mysql.8306.login
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 6
 
Server version: 5.7.20-log MySQL Community Server (GPL)
 
 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
[root@localhost][(none)]> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mysql              |
 
| performance_schema |
 
| sys                |
 
+--------------------+
 
4 rows in set (0.00 sec)
 
 
 
[root@localhost][(none)]> create database db1;
 
Query OK, 1 row affected (0.01 sec)
 
 
 
[root@localhost][(none)]> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| db1                |
 
| mysql              |
 
| performance_schema |
 
| sys                |
 
+--------------------+
 
5 rows in set (0.00 sec)
 
 
 
[root@localhost][(none)]>
 
[root@localhost][(none)]> show slave status\G;
 
*************************** 1. row ***************************
 
               Slave_IO_State:
 
                  Master_Host: 172.16.210.181
 
                  Master_User: rpl_user
 
                  Master_Port: 8306
 
                Connect_Retry: 10
 
              Master_Log_File: mysql-bin.000001
 
          Read_Master_Log_Pos: 150
 
               Relay_Log_File: my1-relay-bin.000003
 
                Relay_Log_Pos: 4
 
        Relay_Master_Log_File: mysql-bin.000001
 
             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: 150
 
              Relay_Log_Space: 721
 
              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: NULL
 
Master_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: 0
 
                  Master_UUID: d14b54b4-de49-11e7-96ea-8ae132e2dda2
 
             Master_Info_File: mysql.slave_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:
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
           Retrieved_Gtid_Set:
 
            Executed_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1
 
                Auto_Position: 1
 
         Replicate_Rewrite_DB:
 
                 Channel_Name:
 
           Master_TLS_Version:
 
1 row in set (0.00 sec)
 
 
 
ERROR:
 
No query specified
 
 
 
[root@localhost][(none)]> 

[root@my2 local]# mysql.8306.login
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 32
 
Server version: 5.7.20-log MySQL Community Server (GPL)
 
 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
[root@localhost][(none)]> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| db1                |
 
| mysql              |
 
| performance_schema |
 
| sys                |
 
+--------------------+
 
5 rows in set (0.00 sec)
 
 
 
[root@localhost][(none)]> create database db2;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
[root@localhost][(none)]> show slave status\G;
 
*************************** 1. row ***************************
 
               Slave_IO_State: Waiting for master to send event
 
                  Master_Host: 172.16.210.180
 
                  Master_User: rpl_user
 
                  Master_Port: 8306
 
                Connect_Retry: 10
 
              Master_Log_File: mysql-bin.000003
 
          Read_Master_Log_Pos: 303
 
               Relay_Log_File: my2-relay-bin.000003
 
                Relay_Log_Pos: 508
 
        Relay_Master_Log_File: mysql-bin.000003
 
             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: 303
 
              Relay_Log_Space: 910
 
              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: 0
 
Master_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: 1808306
 
                  Master_UUID: ce20a632-de49-11e7-9587-c2c763ed137c
 
             Master_Info_File: mysql.slave_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: ce20a632-de49-11e7-9587-c2c763ed137c:1
 
            Executed_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1,
 
d14b54b4-de49-11e7-96ea-8ae132e2dda2:1
 
                Auto_Position: 1
 
         Replicate_Rewrite_DB:
 
                 Channel_Name:
 
           Master_TLS_Version:
 
1 row in set (0.00 sec)
 
 
 
ERROR:
 
No query specified
 
同步正常 

# gtid 故障处理
 
模拟在从库删除库，然后再在主库删除该库，报如下错误
 
 Last_SQL_Error: Error 'Can't drop database 'db1'; database doesn't exist' on query. Default database: 'db1'. Query: 'drop database db1'
 
  Replicate_Ignore_Server_Ids:
 
             Master_Server_Id: 628306
 
                  Master_UUID: 11526eb0-fcbc-11e6-af7d-005056b937e2
 
             Master_Info_File: mysql.slave_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: 170227 15:44:06
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
           Retrieved_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1-2
 
            Executed_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1,
 
1760a7a5-fcbc-11e6-8f14-005056b90358:1
 
                Auto_Position: 1
 
         Replicate_Rewrite_DB:
 
                 Channel_Name:
 
           Master_TLS_Version:
 
 
 
处理方法：stop slave;
 
set gtid_next='11526eb0-fcbc-11e6-af7d-005056b937e2:2';
 
begin;
 
commit;
 
set gtid_next='automatic';
 
start slave;
 
show slave status\G; 

yum install -y pcre-devel openssl-devel popt-devel libnl-* libn*# 安装依赖包
 
#  将 keepalived 配置成系统服务
 
wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
 
tar zxvf keepalived-1.2.7.tar.gz
 
cd keepalived-1.2.7
 
./configure --prefix=/usr/local/keepalived
 
make && make install
 
 
 
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
 
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
 
mkdir /etc/keepalived/
 
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
 
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
 
chmod +x /etc/init.d/keepalived
 
 
 
# server1
 
cat >/etc/keepalived/keepalived.conf <<EOF
 
! Configuration File forkeepalived
 
global_defs {
 
# notification_email {
 
# test@sina.com
 
#  }
 
# notification_email_from  admin@test.com
 
# smtp_server 127.0.0.1
 
# smtp_connect_timeout 30
 
router_id MYSQL_HA18      #标识，双主相同
 
 }
 
vrrp_instance VI_1 {
 
 state BACKUP           #两台都设置 BACKUP
 
 interface eth0
 
 virtual_router_id 18       #主备相同
 
 priority 100           #优先级，backup 设置 90
 
 advert_int 1
 
 nopreempt             #不主动抢占资源，只在 master 这台优先级高的设置，backup 不设置
 
 authentication {
 
 auth_type PASS
 
 auth_pass 1111
 
 }
 
 virtual_ipaddress {172.16.210.183
 
 }
 
}
 
virtual_server 172.16.210.183 8306 {delay_loop 2
 
 #lb_algo rr              #LVS 算法，用不到，我们就关闭了
 
 #lb_kind DR              #LVS 模式，如果不关闭，备用服务器不能通过 VIP 连接主 MySQL
 
 persistence_timeout 50  #同一 IP 的连接 60 秒内被分配到同一台真实服务器
 
 protocol TCP
 
 real_server 172.16.210.180 8306 {   #检测本地 mysql，backup 也要写检测本地 mysql
 
 weight 3
 
 notify_down /usr/local/keepalived/mysql.sh    #当 mysq 服 down 时，执行此脚本，杀死 keepalived 实现切换
 
 TCP_CHECK {connect_timeout 3    #连接超时
 
 nb_get_retry 3       #重试次数
 
 delay_before_retry 3 #重试间隔时间
 
  }
 
}
 
EOF
 
 
 
cat >/usr/local/keepalived/mysql.sh <<EOF
 
#!/bin/bash
 
pkill keepalived
 
EOF
 
chmod +x /usr/local/keepalived/mysql.sh
 
chmod +x /etc/init.d/keepalived
 
/etc/init.d/keepalived start