目录
五、创建多实例数据、日志等文件存放路径,如下树形结构,并更改所属组用户
六、初始化各个实例 [ 初始化完成后会自带随机密码在输出日志中 ]
九、复制多实例脚本到服务管理目录下 [ /etc/init.d/ ],添加脚本执行权限,加进service服务管理
一、下载(mysql-5.7.16.tar.gz)
cd /data/soft/
解压
tar -xvf mysql-5.7.16.tar.gz
二、yum安装相关扩展工具
yum -y install make gcc-c++ cmake bison bison-devel ncurses-devel
yum -yinstalllibaio libaio-devel perl-Data-Dumper net-tools screen
三、下载解压mysql-5.7.16.tar.gz
Boost库是一个经过千锤百炼、可移植、提供源代码的C++库,作为标准库的后备,是C++标准化进程的发动机之一。
tar -jxvf boost_1_59_0.tar.bz2-C /usr/local/
四、编译安装MySQL
cd /data/soft/mysql-5.7.16
cmake -DCMAKE_INSTALL_PREFIX=/data/mysql-5.7.16 -DMYSQL_DATADIR=/data/mysql-5.7.16/data -DSYSCONFDIR==/data/mysql-5.7.16/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/data/mysql-5.7.16/etc/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DENABLE_DOWNLOADS=1 -DWITH_BOOST=/usr/local/boost_1_59_0
#参数说明
参数值说明 | 配置选项 | CMake选项 |
安装根目录 | --prefix=/usr | -DCMAKE_INSTALL_PREFIX=/usr |
mysqld目录 | --libexecdir=/usr/sbin | -DINSTALL_SBINDIR=sbin |
数据存储目录 | --localstatedir=/var/lib/mysql | -DMYSQL_DATADIR=/var/lib/mysql |
配置文件(my.cnf)目录 | --sysconfdir=/etc/mysql | -DSYSCONFDIR=/etc/mysql |
插件目录 | --with-plugindir=/usr/lib64/mysql/plugin | -DINSTALL_PLUGINDIR=lib64/mysql/plugin |
手册文件目录 | --mandir=/usr/share/man | -DINSTALL_MANDIR=share/man |
共享数据目录 | --sharedstatedir=/usr/share/mysql | -DINSTALL_SHAREDIR=share |
Library库目录 | --libdir=/usr/lib64/mysql | -DINSTALL_LIBDIR=lib64/mysql |
Header安装目录 | --includedir=/usr/include/mysql | -DINSTALL_INCLUDEDIR=include/mysql |
信息文档目录 | --infodir=/usr/share/info | -DINSTALL_INFODIR=share/info |
make
make install
useradd mysql -s /sbin/nologin #/sbin/nologin意思是用户不允许登录(通过ftp可以连接)
chown -R mysql:mysql /data/mysql-5.7.16/ #变更所属组、用户
cd /data/mysql-5.7.16/
#配置环境变量
# echo "export PATH=$PATH:/data/mysql-5.7.16/lib:/data/mysql-5.7.16/bin:" >> /etc/profile
# source /etc/profile
五、创建多实例数据、日志等文件存放路径,如下树形结构,并更改所属组用户
1 /data
2 ├── mysql-5.7.16
3 ├── mysql5.7.16data
4 │ ├── mysql3220
5 │ │ ├── data
6 │ │ ├── mysqld.pid
7 │ │ ├── mysql.sock
8 │ │ └── mysql.sock.lock
9 │ └── mysql3221
10 │ ├── data
11 │ ├── mysqld.pid
12 │ ├── mysql.sock
13 │ └── mysql.sock.lock
14 ├── mysql5.7.16log
15 │ ├── mysql3220
16 │ │ ├── binlogs
17 │ │ └── relaylogs
18 │ ├── mysql3221
19 │ │ ├── binlogs
20 │ │ └── relaylogs
21 │ ├── mysqld-5.7-err.log
22 │ ├── mysql_multi.log
23 │ └── slow.log
24 /etc/my.cnf
# mkdir -p/data/mysql5.7.16data/mysql3220
# mkdir -p/data/mysql5.7.16data/mysql3221
# mkdir -p/data/mysql5.7.16log/mysql3220/binlogs
# mkdir -p/data/mysql5.7.16log/mysql3220/relaylogs
# mkdir -p/data/mysql5.7.16log/mysql3221/binlogs
# mkdir -p/data/mysql5.7.16log/mysql3221/relaylogs
# chown -R mysql.mysql /data/mysql5.7.16data/
# chown -R mysql.mysql /data/mysql5.7.16log/
六、初始化各个实例 [ 初始化完成后会自带随机密码在输出日志中 ]
# /data/mysql-5.7.16/bin/mysqld --initialize --user=mysql --basedir=/data/mysql-5.7.16/ --datadir=/data/mysql5.7.16data/mysql3220/data
2018-04-20T08:18:12.096949Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-20T08:18:12.225533Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-04-20T08:18:12.246302Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-04-20T08:18:12.299571Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5df3efc3-4473-11e8-8588-44a842480cba.
2018-04-20T08:18:12.299893Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-04-20T08:18:12.300319Z 1 [Note] A temporary password is generated for root@localhost: dORk8uo,Djqx
# /data/mysql-5.7.16/bin/mysqld --initialize --user=mysql --basedir=/data/mysql-5.7.16/ --datadir=/data/mysql5.7.16data/mysql3221/data
2018-04-20T08:18:23.110503Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-20T08:18:23.236244Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-04-20T08:18:23.256585Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-04-20T08:18:23.310400Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 64840e49-4473-11e8-86e4-44a842480cba.
2018-04-20T08:18:23.310655Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-04-20T08:18:23.311070Z 1 [Note] A temporary password is generated for root@localhost: %i(/69sd3Ryr
七、各实例开启SSL连接(可选)
[root@yz-hgbimdb-02 mysql-5.7.16]# /data/mysql-5.7.16/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/data/mysql-5.7.16/ --datadir=/data/mysql5.7.16data/mysql3221/data
Generating a 2048 bit RSA private key
.....................+++
........+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...........................................................+++
.............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.................................................................................................................+++
............+++
writing new private key to 'client-key.pem'
-----
[root@yz-hgbimdb-02 mysql-5.7.16]# /data/mysql-5.7.16/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/data/mysql-5.7.16/ --datadir=/data/mysql5.7.16data/mysql3220/data
Generating a 2048 bit RSA private key
.................+++
......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...........................................................................................................................+++
...........................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
...............................................................................................................................................+++
......................+++
writing new private key to 'client-key.pem'
-----
八、配置MySQL配置文件/etc/my.cnf
# rmdir /etc/my.cnf.d/
# rsync
# GTID (slave)
log_slave_updates = true
gtid_mode = on
enforce_gtid_consistency = on
#所有slave需要加上skip_slave_start=1的配置参数,避免启动后还是使用老的复制协议。
skip_slave_start=1
# MySQL 5.7开启Enhanced Multi-Threaded Slave配置
# MySQL5.7通过参数--slave-parallel-type=type进行控制并行复制的方式,可选值有DATABASE(默认)和LOGICAL_CLOCK
# DATABASE:默认值,基于库的并行复制方式;LOGICAL_CLOCK:基于组提交的并行复制方式
slave_parallel_type=LOGICAL_CLOCK
# 开启多线程复制
slave_parallel_workers=16
# 以下这两个参数会将master.info和relay.info保存在表(mysql.slave_master_info)中,默认是InnoDB引擎
master_info_repository=TABLE
relay_log_info_repository=TABLE
# 当slave从库宕机后,假如relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从master上获取日志,这样就保证了relay-log的完整性。默认情况下该功能是关闭的,可在slave从库上开启该功能,建议开启。
relay_log_recovery=ON
[mysqld_multi]
mysqld=/data/mysql-5.7.16/bin/mysqld_safe
mysqladmin=/data/mysql-5.7.16/bin/mysqladmin
log=/data/mysql5.7.16log/mysql_multi.log
[mysqld3220]
# 设置监听开放端口 [多实例中一定要不同]
port = 3220
# 设置MySQL实例唯一标识
server-id=703220
# 设置运行用户
user=mysql
# 设置sock存放文件名 [多实例中一定要不同]
socket=/data/mysql5.7.16data/mysql3220/mysql.sock
# 配置MySQL实例启动时进程ID写入文件路径
pid-file=/data/mysql5.7.16data/mysql3220/mysqld.pid
# 设置数据目录 [多实例中一定要不同]
datadir=/data/mysql5.7.16data/mysql3220/data/
# InnoDB表的目录共用设置。如果没有在 my.cnf 进行设置,InnoDB 将使用MySQL的 datadir 目录为缺省目录。如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径。
innodb_data_home_dir=/data/mysql5.7.16data/mysql3220/data/
# 设置InnoDB 日志文件的路径。必须与 innodb_log_arch_dir 设置相同值。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的 ib_logfile... 文件。
innodb_log_group_home_dir=/data/mysql5.7.16data/mysql3220/data/
# 设置innodb 缓存大小
innodb_buffer_pool_size = 20G
# 设置二进制日志
log_bin = /data/mysql5.7.16log/mysql3220/binlogs/log_bin
# 设置中继日志
relay_log=/data/mysql5.7.16log/mysql3220/relaylogs/relay-bin
# 设置字符集
character_set_server = utf8mb4
#跳过mysql程序起动时的字符参数设置 ,使用服务器端字符集设置
skip_character_set_client_handshake = 1
collation_server = utf8mb4_bin
init_connect = 'SET NAMES utf8mb4'
# 设置MySQL提供的group_concat函数可以拼接某个字段值成字符串长度
group_concat_max_len=102400
# 设置监听IP地址
bind_address = 0.0.0.0
# 关闭DNS 反向解析
skip-name-resolve = 0
[mysqld3221]
port = 3221
server-id=703221
user=mysql
socket=/data/mysql5.7.16data/mysql3221/mysql.sock
pid-file=/data/mysql5.7.16data/mysql3221/mysqld.pid
datadir=/data/mysql5.7.16data/mysql3221/data/
innodb_data_home_dir=/data/mysql5.7.16data/mysql3221/data/
innodb_log_group_home_dir=/data/mysql5.7.16data/mysql3221/data/
innodb_buffer_pool_size = 20G
log_bin = /data/mysql5.7.16log/mysql3221/binlogs/log_bin
relay_log=/data/mysql5.7.16log/mysql3221/relaylogs/relay-bin
character_set_server = utf8mb4
skip_character_set_client_handshake = 1
collation_server = utf8mb4_bin
init_connect = 'SET NAMES utf8mb4'
group_concat_max_len=102400
bind_address = 0.0.0.0
skip-name-resolve = 0
其它参数自行配置
九、复制多实例脚本到服务管理目录下 [ /etc/init.d/ ],添加脚本执行权限,加进service服务管理
# cp /data/mysql-5.7.16/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
# chmod +x /etc/init.d/mysqld_multi
# chkconfig --add mysqld_multi
十、查看多实例状态,启动多实例
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3220 isnotrunning
MySQL server from group: mysqld3221 isnotrunning
# mysqld_multi start
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3220 is running
MySQL server from group: mysqld3221 is running
# 查看监听端口
# netstat -lntp | grep mysqld
tcp 0 0 0.0.0.0:3220 0.0.0.0:* LISTEN 32902/mysqld
tcp 0 0 0.0.0.0:3221 0.0.0.0:* LISTEN 32870/mysqld
十一、连接修改密码
# mysql -S /data/mysql5.7.16data/mysql3220/mysql.sock -p'dORk8uo,Djqx'
MySQL>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xxxxx');
MySQL> flush privileges;
MySQL>GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'IDENTIFIED BY 'Ixxxxx' WITH GRANT OPTION;
MySQL> flush privileges;
MySQL> exit;
MySQL # mysql -h127.0.0.1 -P 3220 -uroot -pIp02016@mlS7639
# mysql -S /data/mysql5.7.16data/mysql3221/mysql.sock -p'%i(/69sd3Ryr'
MySQL>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xxxxx');
MySQL> flush privileges;
MySQL>GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'IDENTIFIED BY 'xxxxx' WITH GRANT OPTION;
MySQL> flush privileges;
MySQL> exit;
MySQL # mysql -h127.0.0.1 -P 3221-uroot -pxxxxx
# 停止某一实例
# mysqld_multi stop 3220--password=xxxxx
# 重启
# mysqld_multi start 3220
十二、添加为从库
# cat data/xtrabackup_slave_info
mysql> CHANGE MASTER TO MASTER_LOG_FILE='log_bin.00000*', MASTER_LOG_POS=number
mysql> CHANGE MASTER TO MASTER_HOST='master_ip',MASTER_PORT=port,MASTER_USER='username',MASTER_PASSWORD='password',MASTER_LOG_FILE='log_bin.00000*',MASTER_LOG_POS=number;
基于GTID:CHANGE MASTER TO MASTER_HOST='[IP]',MASTER_PORT=[PORT],MASTER_USER='[USER]',MASTER_PASSWORD='[PASSWD]',MASTER_AUTO_POSITION=1;
十三、拓展之迁移relay log
1、首先停止mysql
2、把relaylog复制到目标路径
# mkdir -p/mysql-5.7.16binlog/relaylogs/
# chown -Rmysql:mysql/mysql-5.7.16binlog/relaylogs/
同时复制relay-bin.index
3、修改my.cnf
# vi /data/mysql-5.7.16/my.cnf
添加一条:relay_log=/mysql-5.7.16binlog/relaylogs/relay-bin
4、然后编辑 relay-bin.index 把路径改为新的绝对路径
编辑relay-log.info文件
5、监控slave同步状态中的:
1)Slave_IO_Running、Slave_SQL_Running状态值,如果都为YES,则表示主从同步;反之,主从不同步。
2)Seconds_Behind_Master的值,如果为0,则表示主从同步不延时,反之同步延时。
Seconds_Behind_Master:是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较,而得到的这么一个差值;
NULL—表示io_thread或是sql_thread有任何一个发生故障,也就是该线程的Running状态是No,而非Yes。
0 — 该值为零,是我们极为渴望看到的情况,表示主从复制良好,可以认为lag不存在。
正值— 表示主从已经出现延时,数字越大表示从库落后主库越多。
负值— 几乎很少见,我只是听一些资深的DBA说见过,其实,这是一个BUG值,该参数是不支持负值的,也就是不应该出现。