MySQL多种实验
MySQL主从复制(两台CentOS7干净的新机器)
*准备两台新机器(我这里演示IP为192.1681.198.6主与192.168.198.72从)
*主服务器操作:先安装数据库yum install mariadb-server -y
创建二进制日志存放目录:mkdir /data/logbin
改变所有者和所属组:chown mysql.mysql /data/logbin
修改配置文件:vim /etc/my.cnf中的[mysqld]添加server-id=1,log_bin=/data/logbin/mysql-bin
重启数据库服务:systemctl restart mariadb
*从服务器操作:先安装数据库yum install mariadb-server -y
修改配置文件:vim /etc/my.cnf中的[mysqld]添加server-id=2,read-only
重启数据库服务:systemctl restart mariadb
*主服务器操作:创建用户参与复制grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';[用户为repluser,口令centos]
查询日志位置:show master logs;[记住此位置]
*从服务器操作:使用CHANGER MASTER TO 命令时指明ssl相关选项,在mysql中输入CHANGE MASTER TO
MASTER_HOST='192.168.198.6',主节点IP
MASTER_USER='repluser',用户
MASTER_PASSWORD='centos',口令
MASTER_PORT=3306,端口
MASTER_LOG_FILE='mysql-bin.000003',日志文件
MASTER_LOG_POS=402;日志文件复制位置
ll /var/lib/mysql查看会发现生成好几个文件,例如:master.info存放主节点用户信息;relay-log.info存放日志复制位置
在mysql中输入:show slave statusG;发现关系已经生成,但线程还未启用
启动线程:start slave;线程启动,也可以用show processlist查看线程;
*测设操作,在主服务器上创建一个数据库:create database db1;
再去从服务器上看show database;会发现已经同步;[也可用其他语句测试,根据自身爱好]
----------------------------------------------------------------------------------------------------------------------------------------------------------
MySQL主从复制(两台CentOS7在已有mysql数据库基础上的机器)
*准备两台新机器(我这里演示IP为192.1681.198.6主与192.168.198.72从)
*主服务器操作:修改配置文件vim /etc/my.cnf中的[mysqld]添加server-id=1,log_bin=/data/logbin/mysql-bin
重启数据库服务:systemctl restart mariadb
创建用户参与复制grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';
备份数据库mysqldump -A --single-transaction --master-data=1 -F > /data/logbin/all.sql[进入查看日志位置]
复制给从服务器:scp /data/logbin/all.sql 192.168.198.72:/data/logbin
*从服务器操作:修改传送过来的文件,如下图所示
修改配置文件:vim /etc/my.cnf中的[mysqld]添加server-id=2,read-only
启动数据库服务:systemctl start mariadb
在mysql中引入文件:source /data/logbin/all.sql
查看:show slave statusG;已经成功[但未启动线程]
启动线程:start slave;启动成功;也可以用命令:ss -nt查看
*测设操作,在主服务器上创建一个数据库:create database db1;
再去从服务器上看show database;会发现已经同步;[也可用其他语句测试,根据自身爱好]
----------------------------------------------------------------------------------------------------------------------------------------------------------
主服务器down，提升一个从服务器成为新的主服务器(一主二从的CentOS7系统)
*一从服务器操作(升主):先停止服务stop slave
删除所有二进制重新计数:reset slave|彻底清楚使用reset slave all(使用all方法)
修改配置文件vim /etc/my.cnf中的[mysqld],下图从服务器配置修改为主服务器配置
重新启动数据库服务:systemctl restart mariadb
进入mysql查看二进制日志文件位置:show master logs;
*二从服务器操作:先停止服务stop slave
删除所有二进制重新计数:reset slave|彻底清楚使用reset slave all(使用all方法)
在mysql终端输入：CHANGE MASTER TO
MASTER_HOST='192.168.198.72',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;
启动服务:start slave;
查看:show slave statusG;已经成功
*测设操作,在主服务器上创建一个数据库:create database db1;
再去从服务器上看show database;会发现已经同步;[也可用其他语句测试,根据自身爱好]
----------------------------------------------------------------------------------------------------------------------------------------------------------
级联复制(三台新主机,作者操作CentOS7系统)
*准备三台新机器(我这里演示IP为192.1681.198.6master服务器配置与192.168.198.72级联从服务器,192.168.198.73从服务器)
*maset服务器操作:先安装数据库yum install mariadb-server -y
创建二进制日志存放目录:mkdir /data/logbin
改变所有者和所属组:chown mysql.mysql /data/logbin
修改配置文件:vim /etc/my.cnf中的[mysqld]添加server-id=1,log_bin=/data/logbin/mysql-bin
启动数据库服务:systemctl start mariadb
创建用户参与复制grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';[用户为repluser,口令centos]
备份数据库mysqldump -A --single-transaction --master-data=1 -F > /data/logbin/all.sql[进入查看日志位置]
*级联从服务器操作:先安装数据库yum install mariadb-server -y
创建文件接收目录:mkdir /data/logbin/
改变所有者和所属组:chown mysql.mysql /data/logbin
*master服务配置操作:复制给从服务器:scp /data/logbin/all.sql 192.168.198.72:/data/logbin
*级联从服务器操作:修改配置文件vim /etc/my.cnf中的[mysqld]添加server-id=2,read-only,log-bin,log_slave_updates
修改传送文件:vim /data/logbin/all.sql,添加CHANGE MASTER TO
MASTER_HOST='192.168.198.6',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;
启动数据库服务:systemctl start mariadb;
把all.sql传入到mysql中:mysql < /data/logbin/all.sql
启动线程服务:mysql > start slave;
备份数据库到二从服务器上:mysqldump -A --single-transaction --master-data=1 -F > /data/logbin/all2.sql
*二从服务器操作:先安装数据库yum install mariadb-server -y
创建文件接收目录:mkdir /data/logbin/
改变所有者和所属组:chown mysql.mysql /data/logbin
*级联从服务配置操作:复制给从服务器:scp /data/logbin/all2.sql 192.168.198.73:/data/logbin
*二从服务器操作:修改配置文件vim /etc/my.cnf中的[mysqld]添加server-id=2,read-only
修改传送文件:vim /data/logbin/all.sql,添加CHANGE MASTER TO
MASTER_HOST='192.168.198.72',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;
启动数据库服务:systemctl start mariadb;
把all.sql传入到mysql中:mysql < /data/logbin/all.sql
启动线程服务:mysql > start slave;
查看:show slave statusG;已经成功[操作没成功报超时错误在级联上刷新flush privileges;]
-------------------------------------------------------------------------------------------------------------------------------------------------------------
MySQL主主复制(两台新机器CentOS系统)
*准备两台新机器(我这里演示为IP为192.168.198.6与192.168.198.72)
*一主操作:先安装数据库yum install mariadb-server -y
修改配置文件:vim /etc/my.cnf在[mysqld]中添加server-id=1,log-bin,auto_increment_offset=1,auto_increment_increment=2
启动数据库服务:systemctl start mariadb
进入mysql终端查看:show master logs;
创建用户grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';
*二主操作:先安装数据库yum install mariadb-server -y
修改配置文件:vim /etc/my.cnf在[mysqld]中添加server-id=2,log-bin,auto_increment_offset=2,auto_increment_increment=2
启动数据库服务:systemctl start mariadb
*一主操作:备份数据库mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql[进入查看日志位置]
复制给从服务器:scp /data/all.sql 192.168.198.72:/data/
*二主操作:修改传送文件vim /data/logbin/all.sql,添加CHANGE MASTER TO
MASTER_HOST='192.168.198.6',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;
把all.sql传入到mysql中:mysql < /data/logbin/all.sql
启动线程服务:mysql > start slave;
查看:show slave statusG;已经成功[单向复制成功]
查看日志位置:show master logs;
*测设操作,在一主服务器上创建一个数据库:create database db1;
再去从服务器上看show database;会发现已经同步;[也可用其他语句测试,根据自身爱好]
*一主操作:在mysql终端输入:CHANGE MASTER TO
MASTER_HOST='192.168.198.72',二主IP
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=514995;二主二进制日志位置
启动线程服务:mysql > start slave;
查看:show slave statusG;已经成功[双向复制成功]
*测设操作,在一主服务器上创建一个数据库:create database db1;
再去从服务器上看show database;会发现已经同步;[也可用其他语句测试,根据自身爱好]
-------------------------------------------------------------------------------------------------------------------------------------------------------------
半同步复制(三台CentOS7新机器)
*准备三台新机器(我这里演示IP为192.1681.198.6主与192.168.198.72从服务器,192.168.198.73从服务器)
*主服务器操作:先安装数据库yum install mariadb-server -y
创建二进制日志存放目录:mkdir /data/logbin
改变所有者和所属组:chown mysql.mysql /data/logbin
修改配置文件vim /etc/my.cnf中的[mysqld]添加server-id=1,log_bin=/data/logbin/mysql-bin
重启数据库服务:systemctl restart mariadb
建用户参与复制grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';
//备份数据库mysqldump -A --single-transaction --master-data=1 -F > /data/logbin/all.sql[进入查看日志位置]
//复制给从服务器:scp /data/logbin/all.sql 192.168.198.72:/data/logbin
*一从服务器操作:先安装数据库yum install mariadb-server -y
修改配置文件vim /etc/my.cnf中的[mysqld]添加server-id=2
重启数据库服务:systemctl restart mariadb
*二从服务器操作:先安装数据库yum install mariadb-server -y
修改配置文件vim /etc/my.cnf中的[mysqld]添加server-id=3
重启数据库服务:systemctl restart mariadb
*主服务器操作:查看日志:show master logs;
创建账号:grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';
*一从与二从相同操作:在mysql终端上输入CHANGE MASTER TO
MASTER_HOST='192.168.198.6',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;
启动服务:start slave;
查看服务是否成功:show slave statusG;
*测设操作,在一主服务器上创建一个数据库:create database db1;
再去从服务器上看show database;会发现已经同步;[也可用其他语句测试,根据自身爱好]
*主服务器操作:安装插件INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
查看是否安装成功:show plugins;
查看插件状态:SHOW GLOBAL VARIABLES LIKE '%semi%';[默认OFF]
启用插件:set global rpl_semi_sync_master_enabled=on;
查看半同步全部状态:SHOW GLOBAL STATUS LIKE '%semi%';
*一从和二从服务器操作:安装插件INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
查看状态:SHOW GLOBAL VARIABLES LIKE '%semi%';[默认OFF]
打开状态:SET GLOBAL rpl_semi_sync_slave_enabled=on;
查看从节点状态:SHOW GLOBAL STATUS LIKE '%semi%';[默认OFF]
重启线程打开状态:stop slave; start slave; SHOW GLOBAL STATUS LIKE '%semi%';查看是ON
*主服务器操作:查看节点是否启用SHOW GLOBAL STATUS LIKE '%semi%';
*测试操作:主服务器操作数据库都会同步/down或者停止服务再或者关闭线程一个从服务器,主服务器操作数据库还会成功,还有一台从服务器
两台从服务器都不在,主服务器操作还会成功,要等待默认时长10秒,两台从服务器重新上线,主服务器操作还会同步上
---------------------------------------------------------------------------------------------------------------------------------------------------------------
利用proxySQL实现读写分离(三台新机器CentOS系统)
*准备三台新机器(演示IP为192.168.198.6proxy与192.168.198.72master,192.168.198.73slave)
*先搭建master与slave的主从复制[请参照第一条例子,再次不多做演示]
*proxy操作不需安装数据库:安装yum仓库cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
安装proxysql:yum install proxysql
安装数据库客户端:yum install mariadb -y
查看proxysql文件:rpm -ql proxysql
启动proxysql服务:service proxysql start
查看端口:ss -ntl(proxysql启动会有6032,6033端口号)
备份一份proxysql配置文件:cp /etc/proxysql.cnf /etc/proxysql.cnf.bak
//修改配置文件改变端口号:vim /etc/proxysql
使用mysqsl客户端连接proxysql:mysql -uadmin -padmin -P6032 -h127.0.0.1
查看数据库:show databases;
main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。 表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改， 只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载
disk 是持久化到硬盘的配置，sqlite数据文件
stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
在数据库中添加72与73记录:insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.198.72',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.198.73',3306);
加载并保存内核生效:load mysql servers to runtime; save mysql servers to disk;
*master操作添加账户:grant replication client on *.* to monitor@'192.168.198.%' identified by 'xdk';
*proxy操作设置监控账号和监控口令:set mysql-monitor_username='monitor';set mysql-monitor_password='xdk';
加载并保存内核生效:load mysql servers to runtime; save mysql servers to disk;
查看监控是否正确:select * from mysql_server_connect_log;
查看客户端状态:select * from mysql_server_ping_log;
设置分组信息,需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段： writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20:insert into mysql_replication_hostgroups values(10,20,"test");
加载并保存生效:load mysql servers to runtime; save mysql servers to disk;
查看分组信息: select hostgroup_id,hostname,port,status,weight from mysql_servers;
*在master创建访问账号:grant all on *.* to sqluser@'192.168.198.%' identified by 'xdk';
*在proxysql把sqluser添加到表中:insert into mysql_users(username,password,default_hostgroup) values('sqluser','xdk',10);[10是写组]
加载并保存生效:load mysql users to runtime;save mysql users to disk;
测试sqluser用户测试是否能成功:mysql -usqluser -pxdk -P6033 -h127.0.0.1 -e 'select @@server_id'
测试sqluser用户是否能创建成功:mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'create database testdb'[在master和从查看]
插入路由规则:insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
保存并生效:load mysql query rules to runtime;save mysql query rules to disk;
查看读写:mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id';
查看:mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'[id为73select为读]
查看调度策略:SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MHA(四台CentOS7新机器)
*准备四台新机器(机器IP为192.168.198.6mha-manager,192.168.198.72master,192.168.198.73slave1,192.168.198.74slave2)
*分别设置四台机器主机名:192.168.198.6hostnamectl set-hostname mha-manager;
192.168.198.72hostnamectl set-hostname master;
192.168.198.73hostnamectl set-hostname slave1;
192.168.198.74hostnamectl set-hostname slave2;
*分别在master,slave1,slave2安装数据库:yum install mariadb -y
*master主,slave1,slave2从复制:修改master配置vim /etc/my.cnf:server-id=72,log-bin,skip_name_resolve
修改slave1,slave2配置vim /etc/my.cnf:server-id=73/74,log-bin,read-only,relay_log_purge=0,skip_name_resolve
启动数据库服务:systemctl start mariadb;
*master操作:创建复制账号:grant replication slave on *.* to repluser@'192.168.198.%' identified by 'centos';
创建mha节点账号:grant all on *.* to mhauser@'192.168.198.%' identified by 'centos';
*slave1,slave2相同操作:在mysql终端输入CHANGE MASTER TO
MASTER_HOST='192.168.198.72',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
启动线程:start slave;
查看线程是否同步:show slave statusG;
*mha-manager操作:链接: https://pan.baidu.com/s/1bZMBoLtXMk8IgGLhSZZSkw 提取码: fpfm 复制这段内容后打开百度网盘
下图两个包复制到系统中
*master,slave1,slave2共同操作:把上图第二个包拉到系统中
*mha-manager操作:查看epel源是否启动yum repolist不存在
安装epel:wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum -y install epel-release
yum repolist
安装mha包:yum install mha*.rpm -y
*master,slave1,slave2共同操作:安装mha包yum install mha*.rpm -y
*mha-manager操作:生成公钥私钥对ssh-keygen
把生成的密钥传送给master,slave1,slave2:scp -r .ssh/ 192.168.198.[72,73,74]:/root/
测试操作:ssh 192.168.198.[72,73,74]链接即成功
创建目录:mkdir /etc/mha
创建文件:vim /etc/mha/app1.cnf[第一个主从]
检查操作:masterha_check_ssh --conf=/etc/mha/app1.cnf[都OK即成功]
检查复制是否正常:masterha_check_repl --conf=/etc/mha/app1.cnf[都OK即成功]
启动:masterha_manager --conf=/etc/mha/app1.cnf[会卡在界面,一次性任务]
关机master主服务器:masterha_manager --conf=/etc/mha/app1.cnf[任务完成,提升73从服务器为主服务器]
*73操作:在MySQL中输入show slave statusG;[没有任何进程,从升主成功]
测试操作:添加一个数据库[同步成功]
show variables like 'read_only';[已经改变OFF],但配置文件未改变,把read-only注销掉
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Galera Cluster(三台新CentOS7机器)
*准备三台新机(演示Ip为192.168.198.6,192.168.198.72,192.168.198.73)
*IP6的节点上操作:yum安装，自建yum源，将自建 yum源传给其他主机，安装相同的MariaDB-Galera-server。如下图
cat > /etc/yum.repos.d/mysql.repo <<EOF
> [mysqsl]
> baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.64/yum/centos7-amd64/
> gpgcheck=0
> EOF
*IP72,73节点执行相同的操作
*三个节点执行相同的操作:安装yum install MariaDB-Galera-server
*节点6操作:修改配置文件vim /etc/my.cnf.d/server.cnf,如下图
把配置好的文件复制并覆盖到其他节点上:scp /etc/my.cnf.d/server.cnf 192.168.198.[72,73]:/etc/my.cnf.d/
首次启动时，需要初始化集群，在其中一个节点上执行命令:/etc/init.d/mysql start --wsrep-new-cluster
而后正常启动其它节点: service mysql start
在各个节点上查看ss -ntl,端口3306已启动
在xshell右下脚选择全部会话输入mysql三台节点机器都进入mysql模式
测试操作:在mysql数据库中创建一个数据库,三个节点机器都会同步
查看集群中相关系统变量和状态变量:SHOW VARIABLES LIKE 'wsrep_%';SHOW STATUS LIKE 'wsrep_%';
----------------------------------------------------------------------------------------------------------------------------------------------------------------