keepalived+nginx+tomcat+redis+mysql主主架构部署(5)

1.8 特别参数说明

log-slave-updates = true         #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
#Master自增长ID auto_increment_offset = 1 auto_increment_increment = 2           #奇数ID
#Backup自增加ID auto_increment_offset = 2 auto_increment_increment = 2           #偶数ID

1.9 添加Backup配置文件/etc/my.cnf

#vim /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
basedir = /usr/local/mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err
server-id = 2
auto_increment_offset = 2
auto_increment_increment = 2                                            #偶数ID
log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项
binlog-format=ROW
#binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M                                                   #binlog单文件最大值
replicate-ignore-db = mysql                                             #忽略不同步主从的
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
max_connections = 3000
max_connect_errors = 30
skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'                                           #连接时执行的SQL
character-set-server=utf8                                               #服务端默认字符集
wait_timeout=1800                                                       #请求的最大连接时间
interactive_timeout=1800                                                #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K
skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet = 16M
[mysqld_safe]

1.10 初始化MySQL

#cd /usr/local/mysql

#./bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

1.11 为启动脚本赋予可执行权限并启动MySQL

#chmod +x /etc/rc.d/init.d/mysqld

#/etc/init.d/mysqld start

2. 配置主从同步

2.1 添加主从同步账户

Mast上：

/usr/local/mysql/bin/mysql -u root -s /home/mysql/mysqld.sock -e "grant all privileges on *.* to xx@'%' identified by 'xx123'。/usr/local/mysql/bin/mysql -u root -s /data/3310/mysqld.sock -e "grant all privileges on *.* to xx@'%' identified by 'xx123'。/usr/local/mysql/bin/mysql -u root -s /data/3311/mysqld.sock -e "grant all privileges on *.* to xx@'%' identified by 'xx123'。

#mysql> flush privileges;

Backup上：

/usr/local/mysql/bin/mysql -u root -s /home/mysql/mysqld.sock -e "grant all privileges on *.* to xx@'%' identified by 'xx123'。/usr/local/mysql/bin/mysql -u root -s /data/3310/mysqld.sock -e "grant all privileges on *.* to xx@'%' identified by 'xx123'。/usr/local/mysql/bin/mysql -u root -s /data/3311/mysqld.sock -e "grant all privileges on *.* to xx@'%' identified by 'xx123'。

#mysql> flush privileges;

2.2 查看主库的状态

Master上：

#mysql> show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                      |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
| mysql-bin.000005 |     5094 |              |                  | 9be18158-3fc0-11e9-b3bc-00505694ed12:1-14,
ae807b12-3fbf-11e9-bd3d-00505694bdbe:1-1620 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Backup上：

#mysql> show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                      |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
| mysql-bin.000005 |     5094 |              |                  | 9be18158-3fc0-11e9-b3bc-00505694ed12:1-14,
ae807b12-3fbf-11e9-bd3d-00505694bdbe:1-1620 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-111706-5.html