1、将主服务器要同步的数据库加锁，避免同步时发生改变:
use database_name;
flush tables with read lock;
2、使用mysqldump工具导出数据:
mysqldump -uroot -pxxx database_name >database_name.sql
mysqldump -uroot -pxxx -R database_name >database_name.sql # -R参数表示同时备份函数
3、将初始数据导入从数据库:
create database database_name;
use database_name;
source database_name.sql;
完成以上操作后，两个服务器就有一样的初态了。
4、备份完成并开始同步后，解锁数据库:（这里先不执行等开始同步）
unlock tables;
主服务my.cnf
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... datadir = /data02/mysql # port = ..... #主主复制配置: auto_increment_offset=2 auto_increment_increment=2 log_bin_trust_function_creators=1 expire_logs_days=30 server_id = 1 socket =/tmp/mysql.sock # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER log_bin = mysql-bin log_bin_index = mysql-bin.index # STATEMENT (statement-based replication, SBR) [default] # ROW (row-based replication, RBR) # MIXED (mixed-based replication, MBR) binlog_format = ROW # less than 1G [default] more than 4kb max_binlog_size = 1G expire_logs_days = 0 # 0 [default] #配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步 sync_binlog = 1 log-slave-updates #中继日志文件 relay_log = mysql-relay-bin #都注释掉表示同步所有库 #需要复制的数据库名，如果有多个数据库，需要重复设置此参数，每个数据库一行 #replicate-do-db=ambari #replicate-do-db=hive #replicate-do-db=ranger #不需要复制的数据库，如果有多个数据库，需要重复设置此参数，每个数据库一行 #replicate-ignore-db=mysql #replicate-ignore-db=information_schema #replicate-ignore-db=performance_schema #replicate-ignore-db=sys slow_query_log = 1 slow_query_log_file = mysql-slow.log log_error = mysql.err log_output = 'FILE,TABLE' max_connections = 16384 max_user_connections = 0 character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci lower_case_table_names=1 # default innodb_default_row_format=DYNAMIC innodb_file_per_table=ON innodb_large_prefix=ON pid-file=/tmp/mysql.pid
从配置my.cnf
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... datadir = /data02/mysql # port = ..... auto_increment_offset=1 auto_increment_increment=2 log_bin_trust_function_creators=1 expire_logs_days=30 server_id = 2 socket =/tmp/mysql.sock # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER log_bin = mysql-bin log_bin_index = mysql-bin.index # STATEMENT (statement-based replication, SBR) [default] # ROW (row-based replication, RBR) # MIXED (mixed-based replication, MBR) binlog_format = ROW # less than 1G [default] more than 4kb max_binlog_size = 1G expire_logs_days = 0 # 0 [default] sync_binlog = 1 log-slave-updates relay_log = mysql-relay-bin #replicate-do-db=ambari #replicate-do-db=hive #replicate-do-db=ranger #replicate-ignore-db=mysql #replicate-ignore-db=information_schema #replicate-ignore-db=performance_schema #replicate-ignore-db=sys slow_query_log = 1 slow_query_log_file = mysql-slow.log log_error = mysql.err log_output = 'FILE,TABLE' max_connections = 16384 max_user_connections = 0 character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci lower_case_table_names=1 # default innodb_default_row_format=DYNAMIC innodb_file_per_table=ON innodb_large_prefix=ON pid-file=/tmp/mysql.pid登录mysql创建用于同步的用户账号:
可在备份之前在主服务上做一次就行，全库备份的话；
在10.218.12.17上执行：
mysql -uroot -p CREATE USER 'repuser'@'%' grant replication slave on *.* to 'repuser'@'10.218.12.21' identified by 'repuser'; flush privileges; select user,host from mysql.user; show grants for repuser@'10.218.12.21';
在10.218.12.21上执行：
mysql -uroot -p CREATE USER 'repuser'@'%' grant replication slave on *.* to 'repuser'@'10.218.12.17' identified by 'repuser'; flush privileges; select user,host from mysql.user; show grants for repuser@'10.218.12.17';服务器开启同步:
在2个master上执行：
flush logs; show master statusG
master_host ： 主服务IP
master_user: 主服务器允许复制的用户名
master_password: 密码
master_log_file ： 当前日志文件的名称 在主服务器上用 show master statusG 查看相关信息
master_log_pos ： 当前日志文件的位置 在主服务器上用 show master statusG 查看相关信息
在2个slave上执行：
change master to master_host='10.218.12.17',master_user='repuser',master_password='repuser',master_log_file=' mysql-bin.000001',master_log_pos= 154,master_port=3306启动slave同步进程
在两个mysql服务器上执行
mysql>start slave; mysql>show slave statusG
查看这两个进程是否yes
Slave_IO_Running: yes
Slave_SQL_Running: yes
在主服务上：
unlock tables;问题：
如果出现’Could not find first log file name in binary log index file’报错Slave_IO_Running: NO
client机器停止slave：stop slave;
master查看：
show master statusG； flush logs; show master statusG；
因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000011
client机器修改位置：
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154; start slave; show slave statusG注意：
如果是做升级，请将mysql相关服务全部关停，不然会出现Slave_SQL_Running:no 的情况；
关闭ambari-server 和hive 所有服务
yum install -y keepalived配置
主使用的mysql上配置
cat /etc/keepalived/keepalived.conf
vrrp_script check_mysql {
script "/usr/sbin/check_mysql.sh"
interval 2
weight 2
}
vrrp_instance MYSQL_HA {
state MASTER
interface eth0
virtual_router_id 50
priority 100
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass password123
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.1.24/24 dev eth0
}
}
次要使用的mysql上配置：
cat /etc/keepalived/keepalived.conf
vrrp_script check_mysql {
script "/usr/sbin/check_mysql.sh"
interval 2
weight 2
}
vrrp_instance MYSQL_HA {
#state MASTER
state BACKUP
#注意使用ifconig 找好网卡有的是bond0
interface eth0
virtual_router_id 50
priority 90
#nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass password123
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.1.24/24 dev eth0
}
}
两个mysql主机配置：
cat /usr/sbin/check_mysql.sh #!/bin/sh a=`ps -aux|grep mysqld|wc -l` if ["$a" -lt 3] sudo systemctl stop keepalived fi
启动keepalived
sudo systemctl start keepalived sudo systemctl status keepalived
