MySQL8.0.12安装主从复制读写分离

centos7.3源码编译安装MySQL8.0.12

--下载mysql8.0.12

https://dev.mysql.com/downloads/mysql/

Archives中选择归档版本，MySQL Community Server:Operating System选择Source Code

0.前期准备条件

#cmake3需要手工下载

#yum -y install gcc gcc-c++ ncurses-devel openssl-devel cmake3 bison wget bzip2

yum install ncurses-devel

yum install bison

#1.cmake的源编译安装

nohup tar zxvf cmake-3.11.1.tar.gz &

cd cmake-3.11.1

./configure

gmake && make install

ldonfig

#2.wolfssl包的部署安装[可选项]，可以不配置

*. 从wolfssl.com网站下载wolfssl-3.13.0.zip压缩包

*. 解压至/opt/mysql-8.0.11/extra/wolfssl-3.13.0目录中。

*. cd wolfssl-3.14.0/IDE/MYSQL

*. 执行do.sh脚本

*. use -DWITH_SSL=wolfssl for CMake

#3.openssl和openssl-devel[本实验使用的是system ssl]

yum install –y openssl openssl-devel

#4.mysql的编译安装

cd /opt

tar zxvf mysql-8.0.12.tar.gz

cd /opt/mysql-8.0.11/

mkdir Zdebug

cd Zdebug

systemctl restart network

--不指定-DDOWNLOAD_BOOST时需要把boost_1_67_0.tar.gz上传到/usr/local/src，mysql8.0.11需要boost_1_66_0.tar.gz

#cmake时注意不要添加如下storage_engine

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_FEDERATED_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

#cmake过程中需要从服务器下载组件，mysql5.7开始编译时不需要指定storage_engine，否则报错：deprecated behivor

#> /opt/mysql-8.0.11/Zdebug/mysql_cmake80.log,加此选项也有可能报错：deprecated behivor

#-DDOWNLOAD_BOOST=1下载boost，若下载过程中出错则再次尝试，若已手工下载则不需要指定-DDOWNLOAD_BOOST=1

cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DENABLED_LOCAL_INFILE=ON

-DWITH_INNODB_MEMCACHED=ON

-DWITH_SSL=system

-DCOMPILATION_COMMENT="zyj_zsd edition"

-DWITH_BOOST=/usr/local/src

-DMYSQL_UNIX_ADDR=/data/mysqldata/3306/mysql.sock

-DSYSCONFDIR=/data/mysqldata/3306

#参考,mysql8.0.12执行

#上传mysql8.0.12到/usr/local/src，并解压

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_DEBUG=0 -DWITH_INNODB_MEMCACHED=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DENABLED_PROFILING=0 -DCMAKE_C_FLAGS="-O3" -DCMAKE_CXX_FLAGS="-O3"

--tail -f /opt/mysql-8.0.11/Zdebug/mysql_cmake80.log

#出现下列日志:

#说明编译成功，其中-DWITH_SSL=system用的是linux操作系统的openssl，需要安装openssl和openssl-devel包，才可以被编译

-- Configuring done

-- Generating done

-- Build files have been written to: /opt/mysql-8.0.11/Zdebug

#如果需要编译安装快速，可以运用多线程加快编译安装，命令如下:

#报错：c++: internal compiler error: Killed (program cc1plus)；解决:内存不足，执行make

make -j 12

make install

#MySQL 8.0的软件目录结构

ls -l /usr/local/mysql/

#5.创建mysql用户&并修改相关文件

groupadd mysql

useradd -g mysql mysql

passwd mysql

#6.设置用户操作系统资源的限制

vi /etc/security/limits.conf

mysql soft nproc 65536

mysql hard nproc 65536

mysql soft nofile 65536

mysql hard nofile 65536

#7.创建MySQL数据目录及赋予相应权限

mkdir -p /usr/local/mysql

mkdir -p /data/mysqldata/{3306/{data,tmp,binlog,innodb_ts,innodb_log},backup,scripts}

chown -R mysql:mysql /data/mysqldata

su - mysql

#8.配置my.cnf文件

vi /data/mysqldata/3306/my.cnf

[client]

port = 3306

socket = /data/mysqldata/3306/mysql.sock

# The MySQL server

[mysqld]

port = 3306

user = mysql

socket = /data/mysqldata/3306/mysql.sock

pid-file = /data/mysqldata/3306/mysql.pid

basedir = /usr/local/mysql

datadir = /data/mysqldata/3306/data

tmpdir = /data/mysqldata/3306/tmp

open_files_limit = 60000

explicit_defaults_for_timestamp

server-id = 1203306

lower_case_table_names = 1

character-set-server = utf8

federated

#sql_mode=STRICT_TRANS_TABLES

max_connections = 1000

max_connect_errors = 100000

interactive_timeout = 86400

wait_timeout = 86400

sync_binlog=0

back_log=100

default-storage-engine = InnoDB

log_slave_updates = 1

#*********** Logs related settings ***********

log-bin = /data/mysqldata/3306/binlog/mysql-bin

binlog_format= mixed

binlog_cache_size=32m

max_binlog_cache_size=64m

max_binlog_size=512m

long_query_time = 1

log_output = FILE

log-error = /data/mysqldata/3306/mysql-error.log

slow_query_log = 1

slow_query_log_file = /data/mysqldata/3306/slow_statement.log

log_bin_trust_function_creators=1

binlog-format=ROW

#log_queries_not_using_indexes

general_log = 0

general_log_file = /data/mysqldata/3306/general_statement.log

#expire-logs-days = 14

binlog_expire_logs_seconds = 1728000

relay-log = /data/mysqldata/3306/binlog/relay-bin

relay-log-index = /data/mysqldata/3306/binlog/relay-bin.index

#****** MySQL Replication New Feature*********

master-info-repository=TABLE

relay-log-info-repository=TABLE

relay-log-recovery

#*********** INNODB Specific options ***********

innodb_buffer_pool_size = 2048M

transaction-isolation=REPEATABLE-READ

innodb_buffer_pool_instances = 8

innodb_file_per_table = 1

innodb_data_home_dir = /data/mysqldata/3306/innodb_ts

innodb_data_file_path = ibdata1:2048M:autoextend

innodb_thread_concurrency = 8

innodb_log_buffer_size = 16M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_log_group_home_dir = /data/mysqldata/3306/innodb_log

innodb_flush_log_at_trx_commit = 2

innodb_max_dirty_pages_pct = 70

innodb_flush_method=O_DIRECT

[mysql]

no-auto-rehash

default-character-set=gbk

prompt = (u@h) [d]>\_

#9.初始化MySQL数据库

/usr/local/src/mysql/bin/mysqld --defaults-file=/data/mysqldata/3306/my.cnf --initialize --user=mysql

#10.启动mysql服务 mysql用户执行

/usr/local/src/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf --user=mysql &

#11.登陆数据库

备注:密码来自于error log日志，明细如下:

cat /data/mysqldata/3306/mysql-error.log | grep tempo

2018-05-16T07:17:57.335486Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: j<ds.k;9j8ra

sudo ln -s /data/mysqldata/3306/mysql.sock /tmp/mysql.sock

/usr/local/src/mysql/bin/mysql -uroot -p'VT%0pSTg/eRY'

--eoror:登陆报错(8.0.11不会报错):mysql Segmentation fault (core dumped)

mysql5对应目录：/media/mysql-5.6.38/cmd-line-utils/libedit

在源码包里,编辑文件/media/mysql-5.6.38/cmd-line-utils/libedit/terminal.c （mysql 安装包所在路径）

把terminal_set方法中的 char buf[TC_BUFSIZE]; 这一行注释,实际mysql8.0.12中未注释

再把 area = buf;改为 area = NULL;

cd /usr/local/src/mysql

cmake .

make

make install

#12.数据库一些基本权限配置

#ALTER USER 'root'@'localhost' IDENTIFIED WITH sha256_password BY 'zsd@7101' PASSWORD EXPIRE INTERVAL 360 DAY;

alter user root@'localhost' identified by 'oracle';

flush privileges;

select host,user,authentication_string from mysql.user;

#root用户赋权和修改密码

update user set host='%' where user='root';

flush privileges;

#默认caching_sh2_passwor密码认证，navicat登陆时：authentication plugin 'caching_sh2_passwor' cannnot be loaded

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'oracle';

grant all privileges on *.* to root@'%';

flush privileges;

--添加到自启动，root用户，服务不能启动，应该mysql用户无权限

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

chmod +x /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig --list

chkconfig --level 345 mysqld on

reboot

--或者，添加到root用户，让root可以登录mysql

vi /etc/profile

export PATH=$PATH:/usr/local/src/mysql/bin

/usr/local/src/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf --user=mysql &

---------------------------------------------------------------------------------------------

--配置主从

#mysql -uroot -poracle -e "show variables like '%log_bin%'"

--1.修改主数据库的的配置文件,修改之后要重启mysql，relay_log_recovery默认值为on

vi /data/mysqldata/3306/my.cnf [mysqld]中增加

server-id=11

log-bin=mysqlmaster-bin.log

sync_binlog=1

#注意：下面这个参数需要修改为服务器内存的70%左右

#添加sqlmode服务无法启动:sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

innodb_buffer_pool_size = 512M

log_bin_trust_function_creators=1

#crash safe安全参数，sync_relay_log为mts为多线程复制时开启；master-info-repository、relay-log-info-repository、relay-log-recovery默认已配置

relay_log_purge=0

#master-info-repository=TABLE

#relay-log-info-repository=TABLE

#relay-log-recovery=1

#sync_relay_log=1

--修改从数据库的的配置文件（从库的server-id不相同）

vi /data/mysqldata/3306/my.cnf [mysqld]中增加

server-id=12

log-bin=mysqlslave-bin.log

sync_binlog=1

#注意：下面这个参数需要修改为服务器内存的70%左右

innodb_buffer_pool_size = 512M

log_bin_trust_function_creators=1

#read_only=1

#crash safe安全参数，sync_relay_log为mts为多线程从库复制

relay_log_purge=0

#master-info-repository=TABLE

#relay-log-info-repository=TABLE

#relay-log-recovery=1

#sync_relay_log=1

-----------------

--参考配置

#主服务器配置 [mysqld]中配置如下

vi /data/mysqldata/3306/my.cnf

#设置服务器id，为1表示主服务器,实例唯一ID，不能和canal的slaveId重复

server_id=1

#启动MySQ二进制日志系统

#选择row模式

binlog-format=ROW

#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行

#binlog-do-db=DB1

#不同步mysql系统数据库

binlog-ignore-db=mysql

#从数据库配置

vi /data/mysqldata/3306/my.cnf

#实例唯一ID，不能和canal的slaveId重复，表示为从数据库

server-id=2

#启动MySQL二进制日志系统

#选择row模式

binlog-format=ROW

#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行

#replicate-do-db=test_master

#不同步mysql系统数据库

replicate-ignore-db=mysql

-----------------

--2.主库和从库执行

mysql -uroot -poracle

create user repl@'192.168.92.19' IDENTIFIED BY 'repl';

create user repl@'192.168.92.18' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.92.19';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.92.18';

flush privileges;

--如下为生产环境中执行

#主数据库锁表（禁止再插入数据以获取主数据库的的二进制日志坐标）：

FLUSH TABLES WITH READ LOCK;

--然后克隆一个SSH会话窗口，在这个窗口打开MySQL命令行：

mysql -uroot -poracle

SHOW MASTER STATUS;

+------------------------+----------+--------------+------------------+-------------------+

+------------------------+----------+--------------+------------------+-------------------+

| mysqlmaster-bin.000001 | 728 | | | |

+------------------------+----------+--------------+------------------+-----------------

#在这个例子中，二进制日志文件是mysqlmaster-bin.000001，位置是728，记录下这两个值，稍后要用到。

#在主数据库上使用mysqldump命令创建一个数据快照：

exit

mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases --triggers --routines --events >all.sql

#解锁主库的锁表操作：

UNLOCK TABLES;

scp all.sql root@192.168.92.19:/root

#从库导入主库的快照：

cd /root

mysql -uroot -poracle -h127.0.0.1 -P3306 < all.sql

--3.从数据库执行

mysql -uroot -poracle

#CHANGE MASTER TO MASTER_HOST='192.168.92.18',master_port = 3306,MASTER_USER='repl',MASTER_PASSWORD='repl';

#指定主库的复制文件位置

stop slave;

CHANGE MASTER TO MASTER_HOST='192.168.92.18',master_port = 3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000018',MASTER_LOG_POS=2313;

#然后启动从数据库的复制线程：

START slave;

--接着查询数据库的slave状态：

SHOW slave STATUS G

#如果下面两个参数都是Yes:Slave_IO_Running:Yes,Slave_SQL_Running:Yes

#若备库Slave_IO_Running异常

#mysql8.0,部署完成后，从库执行，是否能连接主库；可能是源码的原因，需要备库先连接然后执行stop slave和start slave

#备库执行start_slave.sh

vi /home/mysql/start_slave.sh

mysql -s -h192.168.92.18 -urepl -prepl -e "select current_user();"

mysql -s -h192.168.92.19 -uroot -poracle -e "stop slave;"

mysql -s -h192.168.92.19 -uroot -poracle -e "start slave;"

#机器重启后报错：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

解决：

stop slave;

reset slave;

start slave;

-------------------------------------------------------------------------------------------------------

MySQL relay_log_purge=0 时的风险

转自： http://xiezhenye.com/2015/12/mysql-relay_log_purge0-%E6%97%B6%E7%9A%84%E9%A3%8E%E9%99%A9.html

有时候，我们希望将 MySQL 的 relay log 多保留一段时间，比如用于高可用切换后的数据补齐，于是就会设置 relay_log_purge=0，禁止 SQL 线程在执行完一个 relay log 后自动将其删除。但是在官方文档关于这个设置有这么一句话：

Disabling purging of relay logs when using the --relay-log-recovery option risks data consistency and is therefore not crash-safe.

究竟是什么样的风险呢？查找了一番后，基本上明白了原因。

首先，为了让从库是 crash safe 的，必须设置 relay_log_recovery=1，这个选项的作用是，在 MySQL 崩溃或人工重启后，由于 IO 线程无法保证记录的从主库读取的 binlog 位置的正确性，因此，就不管 master_info 中记录的位置，而是根据 relay_log_info 中记录的已执行的 binlog 位置从主库下载，并让 SQL 线程也从这个位置开始执行。MySQL 启动时，相当于执行了 flush logs ，会新开一个 relay log 文件，新的 relay log 会记录在新的文件中。如果默认情况 relay_log_purge=1 时，SQL 线程就会自动将之前的 relay log 全部删除。而当 relay_log_purge=0 时，旧的 relay log 则会被保留。虽然这并不会影响从库复制本身，但还是会有地雷：

由于崩溃或停止 MySQL 时，SQL 线程可能没有执行完全部的 relay log，最后一个 relay log 中的一部分数据会被重新下载到新的文件中。也就是说，这部分数据重复了两次。

如果 SQL 跟得很紧，则可能在 IO 线程写入 relay log ，但还没有将同步到磁盘时，就已经读取执行了。这时，就会造成新的文件和旧的文件中少了一段数据。

如果我们读取 relay log 来获取数据，必须注意这一点，否则就会造成数据不一致。而保留 relay log 的目的也在于此。因此，在处理 relay log 时必须格外小心，通过其中 binlog 头信息来确保正确性。

关于如何配置 crash safe 的复制本身的配置，可以参照：

http://blog.itpub.net/22664653/viewspace-1752588/

http://www.innomysql.net/article/34.html

参考资料：

http://blog.booking.com/better_crash_safe_replication_for_mysql.html

https://bugs.mysql.com/bug.php?id=73038

http://bugs.mysql.com/bug.php?id=74324

----------------------------------------------------------------------------------------------------------------------

#配置主从库加密

#主从执行

vi /data/mysqldata/3306/my.cnf

[mysqld]

early-plugin-load=keyring_file.so

keyring_file_data=/usr/local/mysql/keyring/keyring

#主从执行

#添加目录并赋予权限

mkdir -p /usr/local/mysql/keyring

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

chmod -R 750 /usr/local/mysql/keyring/

#主从执行安装插件,插件名称：keyring_file

mysql -uroot -poracle

show variables like '%keyring_file%';

INSTALL PLUGIN keyring_file soname 'keyring_file.so';

#主从重启mysqld

systemctl restart mysqld

#主从执行

show plugins;

#未查询到early-plugin-load对应的属性

show variables like '%early-plugin-load%';

show variables like '%keyring_file%';

#查询注册的插件

select * from mysql.plugin;

#查看所加插件是否生效：keyring_file ACTIVE

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

#查看 innodb_file_per_table参数是否已经为开（default:ON）

show variables like '%innodb_file_per_table%';

#主执行创建加密表空间

create database test;

use test;

#CREATE TABLE test.t1 (c1 INT) ENCRYPTION='Y';

CREATE TABLE test.t1 (c1 INT);

alter table test.t1 add column v_varchar varchar(200) default 'var100';

insert into test.t1 select 1,'n1';

CREATE TABLE test.t123 (id INT,name varchar(200));

insert into test.t123 select 1,'n1';

#已存在表加密

ALTER TABLE test.t1 ENCRYPTION='Y';

#查看所有加密表（表空间）

select engine,TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS from information_schema.tables where create_options like '%ENCRYPTION%="Y"%' or create_options like '%ENCRYPTION%=''Y''%';

------------------------------------------------------------------------------------------------------

--读写分离

cd /opt

rpm -ivh jdk-8u162-linux-x64.rpm

tar -zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

vi /etc/profile

export JAVA_HOME=/usr/java/latest

#export CLASSPATH=.:$JAVA_HOME/lib

#export PATH=$JAVA_HOME/bin:$PATH

export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$CLASSPATH

export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH

export MYCAT_HOME=/opt/mycat

export PATH=$PATH:$MYCAT_HOME/bin

source /etc/profile

--如果配置文件不正确，则导致：JVM did not exit on request, terminated

--如下配置company表只允许在主库写而不允许读

vi schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

<!--

<heartbeat>select user()</heartbeat>

</dataHost>

-->

</mycat:schema>

--最后的user配置如下，注释掉最后的多余的mycat的schema:TESTDB

vi server.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat="http://io.mycat/">

</user>

</user>

</mycat:server>

--mycat启动错误

Startup failed: Timed out waiting for a signal from the JVM.

JVM did not exit on request, terminated

解决办法：在wrapper.conf中添加，本例中少wrapper.startup.timeout

vi wrapper.conf 添加如下

#超时时间300秒

wrapper.startup.timeout=300

#wrapper.ping.timeout=120

--修改日志模式为debug

vi /opt/mycat/conf/log4j2.xml info修改为debug

</asyncRoot>

--以前的版本参考：

<root>

<appender-ref ref="FILE" />

</root>

--修改conf下的配置后可能导致mycat服务起不来

--启动mycat 参考startup_nowrap.sh

mycat start

--navicat通过8066端口连接mycat，如下为命令行方式连接mycat

mysql -uroot -h192.168.92.18 -P8066 -poracle

--查询debug日志，如下为日志中对应的执行的sql和对应的执行节点，也可以根据attachment关键字确定

tail -f /opt/mycat/logs/mycat.log

to send query cmd:

select user()

in pool

DBHostConfig [hostName=myhostM1, url=192.168.92.18:3306]

--navicat测试读写分离

select * from t1;

select * from test.t1;

insert into test.t1 select 4,'user4';

--只能写，读报错

select * from orderdb.company ;

insert into orderdb.company select 6,'company6';