MySQL主主配置
首先在要做主主的2台MySQL数据库服务器上安装数据库软件，我这里是编译安装的方式（顺便记录一下编译安装），其他方式也可。
两台服务器地址分别是
10.19.100.13
10.19.100.14
环境
REHL 6.3
percona-server-5.7.19-17.tar.gz
1. 卸载旧版MySQL
rpm -qa|grep mysql mysql-libs-5.1.61-4.el6.x86_64 mysql-devel-5.1.61-4.el6.x86_64 mysql-5.1.61-4.el6.x86_64 rpm -e --nodeps mysql-libs-5.1.61-4.el6.x86_64 rpm -e --nodeps mysql-devel-5.1.61-4.el6.x86_64 rpm -e --nodeps mysql-5.1.61-4.el6.x86_64 rm -rf /etc/my.cnf
2. 检查编译依赖包
rpm -q gcc gcc-c++ ncurses-devel cmake libaio bison zlib-devel gcc-4.4.6-4.el6.x86_64 gcc-c++-4.4.6-4.el6.x86_64 ncurses-devel-5.7-3.20090208.el6.x86_64 package cmake is not installed libaio-0.3.107-10.el6.x86_64 bison-2.4.1-5.el6.x86_64 zlib-devel-1.2.3-27.el6.x86_64
补全cmake
tar zxvf cmake-2.8.5.tar.gz cd cmake-2.8.5 ./bootstrap make make install
3. mysql-5.7编译安装需要boost
tar zxvf boost_1_59_0.tar.gz
mv boost_1_59_0 /mysql/boost
4. 编译安装mysyql percona server
tar zxvf percona-server-5.7.19-17.tar.gz cd percona-server-5.7.19-17 cmake . -DCMAKE_INSTALL_PREFIX=/mysql/5.7/ \ #MySQL安装位置 -DSYSCONFDIR=/mysql/5.7/ \ #MySQL单实例启动配置文件查找位置 -DMYSQL_DATADIR=/mysql/5.7/data/ \ #MySQL数据文件默认位置 -DEXTRA_CHARSETS=all \ #字符集支持 -DDEFAULT_CHARSET=utf8 \ #默认字符集 -DWITH_BOOST=/mysql/boost \ #boost地址 -DDEFAULT_COLLATION=utf8_general_ci #默认字符核准集 make make install
5. 改权限和环境变量
chown -R mysql:mysql /mysql/ vi /home/mysql/.bash_profile export MYSQL_HOME=/mysql/5.7 PATH=$PATH:$HOME/bin:$MYSQL_HOME/bin export PATH
6. 修改文件句柄限制
vi /etc/security/limits.conf mysql hard nproc 16384 mysql hard nofile 65535
7. 准备参数文件
my.cnf可以通过percona网站按你的需求生成 https://tools.percona.com/
放置在编译时-DSYSCONFDIR指定的目录下
8. 初始化数据库
mysqld --initialize --datadir=/mysql/5.7/data --user=mysql
在错误日志中会有mysql root用户初始随机密码
不指定日志时会直接屏幕输出
[Note] A temporary password is generated for root@localhost: vr&iEb5%ASl*
9. 启动
support-files/mysql.server start Starting MySQL (Percona Server).[ OK ] ps -ef|grep mysql mysql 26640 1 0 13:55 pts/0 00:00:00 /bin/sh /mysql/5.7/bin/mysqld_safe --datadir=/mysql/5.7/data/ --pid-file=/mysql/5.7/data/mysql.pid mysql 27111 26640 1 13:55 pts/0 00:00:01 /mysql/5.7/bin/mysqld --basedir=/mysql/5.7 --datadir=/mysql/5.7/data --plugin-dir=/mysql/5.7/lib/mysql/plugin --log-error=/mysql/5.7/data/mysql-error.log --open-files-limit=65535 --pid-file=/mysql/5.7/data/mysql.pid --socket=/mysql/5.7/data/mysql.sock 修改root初始密码 alter user 'root'@'localhost' identified by '123456';
10. MySQL双主（双活）配置
13上
除percona生成参数外必要参数
# 2 Master server-id = 1 log-bin = /mysql/5.7/data/mysql-bin binlog_format = mixed auto_increment_offset = 1 auto_increment_increment = 2 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema log-slave-updates = true
设置流复制用户
grant replication slave on *.* to 'repl'@'10.19.100.14' identified by '123456'; flush privileges; show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1016 | | | | +------------------+----------+--------------+------------------+-------------------+
14上
除percona生成参数外必要参数
# 2 Master server-id = 1 log-bin = /mysql/5.7/data/mysql-bin binlog_format = mixed auto_increment_offset = 2 auto_increment_increment = 2 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema log-slave-updates = true
设置流复制用户
grant replication slave on *.* to 'repl'@'10.19.100.13' identified by '123456'; flush privileges; show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1016 | | | | +------------------+----------+--------------+------------------+-------------------+
可以查看主库当前状态命令
show master status;
13上
change master to master_host='10.19.100.14',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1016; start slave; show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.19.100.14 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1016 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema 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: 1016 Relay_Log_Space: 531 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: 2 Master_UUID: c3b884af-b235-11e7-a5f6-080027a0e082 Master_Info_File: /mysql/5.7/data/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: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
14上
change master to master_host='10.19.100.13',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1016; start slave; show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.19.100.13 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1016 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema 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: 1016 Relay_Log_Space: 531 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: 1 Master_UUID: 457613ff-b233-11e7-ab5f-080027a0e082 Master_Info_File: /mysql/5.7/data/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: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
