mysql 实验_MySQL多种实验

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 status\G;发现关系已经生成,但线程还未启用

启动线程: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 status\G;已经成功[但未启动线程]

启动线程: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 status\G;已经成功

*测设操作,在主服务器上创建一个数据库: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 status\G;已经成功[操作没成功报超时错误在级联上刷新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 status\G;已经成功[单向复制成功]

查看日志位置: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 status\G;已经成功[双向复制成功]

*测设操作,在一主服务器上创建一个数据库: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 status\G;

*测设操作,在一主服务器上创建一个数据库: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 <

[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 status\G;

*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 status\G;[没有任何进程,从升主成功]

测试操作:添加一个数据库[同步成功]

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 <

> [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_%';

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