mysql主主和F5_mysql主从&主主部署记录

本博客为mysql安装实际案例记录

系统版本centos7.5

mysql:mysql-5.7.22.tar.gz

安装之前环境：干净版本centos7.5

# whereis mysql

mysql: /usr/lib64/mysql /usr/share/mysql

# rpm -qa | grep "mariadb"

mariadb-libs-5.5.56-2.el7.x86_64

需要软件包：

mysql源码

网易镜像下载地址：http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.22.tar.gz

官方：https://downloads.mysql.com/archives/community/

操作系统类型要选择source code，才是编译安装要用到的源码包；然后到底部，下载mysql-5.7.22.tar.gz

mysql5.7.22编译需要依赖boost包

boost1.5.9 下载地址：http://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

安装步骤：

1.卸载mariadb

查询

# rpm -qa | grep "mariadb"

mariadb-libs-5.5.56-2.el7.x86_64

卸载

# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps

警告：/etc/my.cnf 已另存为 /etc/my.cnf.rpmsave

2.上传并解压部署包

cd /usr/local/src/;tar -zxvf boost_1_59_0.tar.gz -C /usr/local/

cd /usr/local/src/;tar -zxvf mysql-5.7.22.tar.gz -C /usr/local/src/

3.安装依赖包

yum -y install make cmake gcc gcc-c++ bison bison-devel ncurses ncurses-devel autoconf automake wget

4.创建mysql安装目录和数据目录

mkdir -p /usr/local/mysql

mkdir -p /r2/mysqldata

5.添加用户并赋予对应目录权限

useradd mysql

chown -R mysql:mysql /r2/mysqldata

chown -R mysql:mysql /usr/local/mysql

6.检测和配置mysql编译环境

cd /usr/local/src/mysql-5.7.22 && cmake \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_UNIX_ADDR=/r2/mysqldata/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DSYSCONFDIR=/etc \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_MEMORY_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_DATADIR=/r2/mysqldata \

-DMYSQL_USER=mysql \

-DMYSQL_TCP_PORT=3306 \

-DWITH_BOOST=/usr/local/boost_1_59_0

7.编译

cd /usr/local/src/mysql-5.7.22 && make -j4

8.安装

cd /usr/local/src/mysql-5.7.22 && make install

9.添加mysql的环境变量

vim /etc/profile

PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH

export PATH

让其生效source /etc/profile

10.初始化mysql

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/r2/mysqldata

获取初始化密码# grep "root@localhost" /r2/mysqldata/error.log |awk '{print $NF}'

IpRu+>C1_KZ7

初始化失败需要删掉/r2/mysqldata的目录内容

11.把mysql-server加入服务初始点

vim /etc/init.d/mysqld添加下面安装位置

basedir=/usr/local/mysql

datadir=/r2/mysqldata

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

centos7：

cat < /usr/lib/systemd/system/mysql.service

[Unit]

Description=Mysql

After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]

Type=forking

ExecStart=/usr/local/mysql/support-files/mysql.server start

ExecReload=/usr/local/mysql/support-files/mysql.server restart

ExecStop=/usr/local/mysql/support-files/mysql.server stop

LimitNOFILE = 65535

PrivateTmp=false

[Install]

WantedBy=multi-user.target

EOF

systemctl daemon-reload

systemctl enable mysql

systemctl start mysql

centos6：

shell> cd /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/support-files/

shell> cp mysql.server /etc/init.d/mysqld

shell> chmod +x /etc/init.d/mysqld

shell> chkconfig --add mysqld

shell> chkconfig --list mysqld

shell> service mysqld start

shell> service mysqld status

12.启动 MySQL 服务

启动脚本start.sh

#!/bin/bash

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/r2/mysqldata &

停止脚本stop.sh：

#!/bin/bash

/usr/local/mysql/bin/mysqladmin -uroot -pmaef123 shutdown &

13.设置mysql服务开机自启动

chkconfig mysql on

14.登陆mysql并授权

/usr/local/mysql/bin/mysql -uroot -p之后输入密码IpRu+>C1_KZ7

set password=password("maef123");

#新建账号wallet，程序访问,只能本地登陆

grant insert,update,delete,select,create,drop,index,trigger,alter on *.* to wallet@'%' identified by 'maef123';

grant insert,update,delete,select,create,drop,index,trigger,alter on *.* to wallet@'localhost' identified by 'maef123';

#通用账户

grant RELOAD,REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@'%' identified by 'maef';

#备份账号dbbackup

grant SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS,SUPER,CREATE,SHOW DATABASES,SHOW VIEW, EVENT, TRIGGER,create tablespace on *.* to dbbackup@'localhost' identified by 'maef123';

#监控账号monitor

grant SELECT, PROCESS, REPLICATION CLIENT, SHOW DATABASES on *.* to monitor@'%' identified by 'maef';

flush privileges;

15.mysql主从搭建

主库的server-id = 1

从库设置为server-id = 2

master主库：# /usr/local/mysql/bin/mysql -uroot -pmaef123 -e "show master status \G";

mysql: [Warning] Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

File: binlog.000007

Position: 194

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: bd2389f5-a694-11e8-8afd-000c29405632:1-7

slave从库：登陆从库/r2/mysql/bin/mysql -uroot -p123456

(root@localhost)10:09:12 [(none)]>change master to master_host='192.168.30.36',master_port=3306,master_user='repl',master_password='maef123',master_log_file='binlog.000007',master_log_pos=194;

(root@localhost)10:09:12 [(none)]>start slave;

(root@localhost)10:09:12 [(none)]>show slave status\G

Slave_IO_Running 和 Slave_SQL_Running 都为 Yes

16.mysql主主搭建：

192.168.1.2的/etc/my.cnf

server-id = 1-----不可以相同

auto-increment-increment = 2

auto-increment-offset = 1------不可以相同

192.168.1.3的/etc/my.cnf这样配置

server-id = 2

auto-increment-increment = 2

auto-increment-offset = 2

分别在192.168.1.2和3上面执行命令获取同步点

root@192.168.1.2# /usr/local/mysql/bin/mysql -uroot -pmaef123 -e "show master status \G";

mysql: [Warning] Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

File: binlog.000007

Position: 194

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: bd2389f5-a694-11e8-8afd-000c29405632:1-7

root@192.168.1.3# /usr/local/mysql/bin/mysql -uroot -pmaef123 -e "show master status \G";

mysql: [Warning] Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

File: binlog.000008

Position: 228

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: bd2389f5-a694-11e8-8afd-000c29405632:1-7

分别在192.168.1.2和3上面做同步

root@192.168.1.2# /usr/local/mysql/bin/mysql -uroot -pmaef123

(root@localhost)10:09:12 [(none)]>change master to master_host='192.168.1.3',master_port=3306,master_user='repl',master_password='maef123',master_log_file='binlog.000008',master_log_pos=228;

(root@localhost)10:09:12 [(none)]>start slave;

(root@localhost)10:09:12 [(none)]>show slave status\G

Slave_IO_Running 和 Slave_SQL_Running 都为 Yes

root@192.168.1.3# /usr/local/mysql/bin/mysql -uroot -pmaef123

(root@localhost)10:09:12 [(none)]>change master to master_host='192.168.1.2',master_port=3306,master_user='repl',master_password='maef123',master_log_file='binlog.000007',master_log_pos=194;

(root@localhost)10:09:12 [(none)]>start slave;

(root@localhost)10:09:12 [(none)]>show slave status\G

Slave_IO_Running 和 Slave_SQL_Running 都为 Yes

16.配置my.cnf内容

# line :V1.0

# file_name :my.cnf

# update :调整innodb_open_files设置值，必须小于open_files_limit的设置值

#### 注意 :建议参数根据实际情况作调整

#### 本配置文件主要适用于MySQL 5.7.22版本

# ********* 以下重要参数必须修改核对 *********

# 1.innodb_flush_log_at_trx_commit=2

# 2.sync_binlog = 0

# 3.innodb_strict_mode = OFF #关闭InnoDB严格检查模式

# 4.innodb_flush_method = O_DIRECT

# 5.lower_case_table_names = 0 #设置区分大小写

# 6.character-set-server = utf8

# 7.sql_mode #配置为空值

# 8.server-id =1 #修改成对应数值

# 9.innodb_buffer_pool_size = 10G #纯mysql server 配置50%和 混合内存配置不低于10G~40%

#10.key_buffer_size=1G #如果有myisam表请配置为1G，没有请配置64M

#11.innodb_data_file_path = ibdata1:1G:autoextend #确认配置是否跟原来一样,之前已配置好请维持原样,如未配置请注释掉，新版本请取消注释

#12.log_bin = /r2/mysqldata/binlog #旧版本或者之前已配置好如：log_bin =/r2/mysqldata/slave-bin，请维持原样

#13.slave-parallel #从库开启并行复制，并行复制参数取消注释

#14.undolog #确认配置是否跟原来一样,之前已配置好请维持原样,如未配置请注释掉，新版本(包括升级版本)请取消注释并创建目录并授权

# ********************************************

[client]

port = 3306

default-character-set = utf8

socket = /r2/mysqldata/mysql.sock

#=======================================================================

# # MySQL客户端配置

#=======================================================================

[mysql]

prompt="(\u@\h) \\R:\\m:\\s [\d]> "

no-auto-rehash

default-character-set = utf8

#=======================================================================

# MySQL服务器全局配置

#=======================================================================

[mysqld]

user = mysql

port = 3306

server-id = 1

basedir = /usr/local/mysql

tmpdir = /r2/mysqldata

datadir = /r2/mysqldata

socket = /r2/mysqldata/mysql.sock

log-error=/r2/mysqldata/error.log

wait_timeout = 31536000

#interactive_timeout = 600

sql_mode = #sql_mode 配置为空值

#skip_name_resolve = 1

skip_name_resolve #开启dns解析

lower_case_table_names = 0

character-set-server = utf8

collation-server = utf8_unicode_ci

#skip-character-set-client-handshake #忽略客户端的字符集，使用服务器的设置

#auto_increment_increment = 1

#auto_increment_offset = 1

log_timestamps = SYSTEM

init_connect= 'SET NAMES utf8'

max_allowed_packet = 128M

######################### 性能参数 ####################

open_files_limit = 10240

max_connections = 10000

max_user_connections=9990

max_connect_errors = 100000

table_open_cache = 1024

thread_cache_size = 64

max_heap_table_size = 32M

query_cache_type = 0

###global cache ###

key_buffer_size = 64M

query_cache_size = 0

tmp_table_size = 32M #内存临时表

binlog_cache_size = 4M #二进制日志缓冲

###session cache ###

sort_buffer_size = 8M #排序缓冲

join_buffer_size = 4M #表连接缓冲

read_buffer_size = 8M #顺序读缓冲

read_rnd_buffer_size = 8M #随机读缓冲

thread_stack = 256KB #线程的堆栈的大小

######################### binlog设置 #####################

binlog_format = MIXED

log_bin = /r2/mysqldata/binlog

max_binlog_size = 1G

expire_logs_days = 3 #binlog比较占空间，注意磁盘空间

sync_binlog = 0 #重要参数必须修改为0

######################### 复制设置 ########################

log_slave_updates = 1

#replicate-do-db = test

#binlog-ignore-db = mysql

### GTID 配置 ###

gtid_mode=ON

enforce-gtid-consistency=true

#****************** 开启并行复制(从库)******************

#slave-parallel-type=LOGICAL_CLOCK #基于组提交的并行复制方式

#slave-parallel-workers=8 #并行的SQL线程数量

#master-info_repository=TABLE #master信息以表的形式保存

#relay_log_info_repository=TABLE #slave信息以表的形式保存

#relay_log_recovery=ON #relay_log自我修复

######################### innodb ##########################

default_storage_engine = InnoDB

#innodb_data_file_path = ibdata1:1G:autoextend

innodb_buffer_pool_size = 128M #系统内存50%

innodb_open_files = 5120

innodb_flush_log_at_trx_commit = 2 #线上服务器必须配置为2

innodb_file_per_table = 1

innodb_lock_wait_timeout = 5

innodb_io_capacity = 200 #根据您的服务器IOPS能力适当调整innodb_io_capacity，配SSD盘可调整到 10000 - 20000

innodb_io_capacity_max = 20000

innodb_flush_method = O_DIRECT

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_large_prefix = 0

innodb_thread_concurrency = 64

innodb_strict_mode = OFF

innodb_sort_buffer_size = 4194304

#****************** undolog设置 ******************

#innodb_undo_directory = /r2/undolog #undolog日志目录

#innodb_undo_tablespaces = 2 #undolog日志文件个数，mysql8之后将弃用

#innodb_undo_logs = 128 #回滚段的数量， 至少大于等于35，默认128。

#innodb_max_undo_log_size = 1G #当超过这个阀值(默认是1G)，会触发truncate回收(收缩)动作，truncate后空间缩小到10M。

#innodb_purge_rseg_truncate_frequency = 128 #控制回收(收缩)undolog的频率

#innodb_undo_log_truncate = 1 #即开启在线回收undolog日志文件

######################### log 设置 #####################

log_error = /r2/mysqldata/error.log

slow_query_log = 1

long_query_time = 10

slow_query_log_file = /r2/mysqldata/slow.log

#=======================================================================

# MySQL mysqldump配置

#=======================================================================

[mysqldump]

quick

max_allowed_packet = 128M

#=======================================================================

# MySQL mysqld_safe配置

#=======================================================================

[mysqld_safe]

log_error = /r2/mysqldata/error.log

pid_file = /r2/mysqldata/mysqldb.pid