mysql主主配置文件_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 cmakeisnot 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-17cmake .-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.7PATH=$PATH:$HOME/bin:$MYSQL_HOME/bin

export PATH

6. 修改文件句柄限制

vi /etc/security/limits.conf

mysql hard nproc16384mysql hard nofile65535

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

mysql26640 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

mysql27111 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生成参数外必要参数

# 2Master

server-id = 1log-bin = /mysql/5.7/data/mysql-bin

binlog_format=mixed

auto_increment_offset= 1auto_increment_increment= 2replicate-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生成参数外必要参数

# 2Master

server-id = 1log-bin = /mysql/5.7/data/mysql-bin

binlog_format=mixed

auto_increment_offset= 2auto_increment_increment= 2replicate-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: Waitingfor master to send eventMaster_Host:10.19.100.14Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:1016Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos:320Relay_Master_Log_File: mysql-bin.000002Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:1016Relay_Log_Space:531Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:2Master_UUID: c3b884af-b235-11e7-a5f6-080027a0e082

Master_Info_File:/mysql/5.7/data/master.info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingformore updates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0Replicate_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: Waitingfor master to send eventMaster_Host:10.19.100.13Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:1016Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos:320Relay_Master_Log_File: mysql-bin.000002Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:1016Relay_Log_Space:531Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: 457613ff-b233-11e7-ab5f-080027a0e082

Master_Info_File:/mysql/5.7/data/master.info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingformore updates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version: