mysql5 编译安装
一、mysql5 编译安装
1、安装软件cmake
yum install cmake -y
2、下载mysql
lftp 172.25.254.250
get mysql-boost-5.7.31.tar.gz
tar zxf mysql-boost-5.7.31.tar.gz
3、安装mysql
mkdir /data/mysql -p
useradd -M -s /sbin/nologin mysql
chown mysql.mysql /data/mysql/
cd mysql-5.7.31
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc -DENABLED_LOCAL_INFILE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.31/boost/boost_1_59_0/ ##源码编译
yum install -y ncurses-devel.x86_64
rm -rf CMakeCache.txt ##清除缓存
yum install -y bison
yum install -y gcc-c++
rm -rf CMakeCache.txt
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc -DENABLED_LOCAL_INFILE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.31/boost/boost_1_59_0/
make
make install
3、编译mysql
cd
vim .bash_profile
source .bash_profile
cd /user/local/mysql/support-files
cp mysql.server /etc/init.d/mysqld
mysql --initialize --user=mysql ##初始化mysql 显示初始密码(如果格式化不成功,删掉 /data/mysql/* 再次格式化)
vim /etc/my.cnf
/etc/init.d/mysqld start
mysql_secure_installation
mysql -p
show databases;
下载mysql图形工具
:phpMyAdmin
lftp 172.25.254.150
cd pub/docs/lamp
get
phpMyAdmin-5.0.2-all-languages.zip
exit
下载解压
工具:unzip
yum install unzip -y
unzip phpMyAdmin-5.0.2-all-languages.zip ##解压
mv phpMyAdmin-5.0.2-all-languages /usr/local/nginx/html/phpadmin
cd /usr/local/nginx/html/phpadmin
ls
systemctl start php-fpm.service
systemctl enable php-fpm.service
vim conf/nginx.conf
nginx -s reload
cd /usr/local/php/
php -m |grep mysql
cd /usr/local/php/etc/
ls
vim php.ini
systemctl reload php-fpm.service
mysql -p
二、mysql主从复制
server1 主
server2 从
server1
:
scp -r /usr/local/mysql server2:/usr/local/
mysql -p
mysql> create database rtt;
mysql> use rtt;
mysql> insert into zy (user,passwd) value ('zy1','123');
mysql> select * from zy;
mysql> ^DBye
mysqldump -uroot -p rtt > /root/rtt.db ##备份
server2
:
cd
vim .bash_profile
source .bash_profile
vim /etc/my.cnf
mkdir -p /data/mysql
useradd -M -d /data/mysql -s /sbin/nologin mysql
chown mysql:mysql /data/mysql
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql_secure_installation
mysql -p
mysql> show databases;
mysql> ^DBye
scp server1:/root/rtt.db /root/
mysql -uroot -pwestos -e "create database rtt;"
mysql -uroot -p rtt < rtt.db
测试
:
mysql -p
mysql> select * from rtt.zy;
主从数据同步
server1
:
vim /etc/my.cnf
/etc/init.d/mysqld restart
mysql -p
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos'; # % 表示可以通过localhost及网络登陆
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #给所有通过repl用户访问的slave在*.*(所有库所有表上)授权REPLICATION
server2
:
vim /etc/my.cnf
/etc/init.d/mysqld restart
mysql -p
mysql> set global server_id=2;
mysql> show variables like 'server_id';
mysql> ^DBye
server1
:
mysql -p
mysql> show master status; ##查看
server2
:
mysql -p
mysql> change master to master_host='172.25.72.1', master_user='repl',master_password='westos',master_log_file='mysql-bin.000001',master_log_pos=1200;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
mysql> show slave status\G;
mysql> quit
测试
:
三、mysql服务
1、GTID复制
实现主从
server1
:
vim /etc/my.cnf
/etc/init.d/mysqld restart
server2
:
vim /etc/my.cnf
/etc/init.d/mysql restart
mysql -p
mysql> stop slave;
mysql> change master to master_host='172.25.72.1', master_user='repl', master_password='westos', MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;
测试
:
server1
:
mysql -p
mysql> use rtt;
mysql> insert into zy (user,passwd) value ('rtt2','123');
mysql> ^DBye
server2
:
mysql> select * from rtt.zy;
实现 主 —>从(主)—> 从
server1
server2
server3
server2:
vim /etc/my.cnf
mysql -p
mysql> create user 'repl'@'%' identified by 'westos'; ##创建用户
mysql> grant replication slave on *.* to 'repl'@'%';
mysqldump -p rtt > /root/dump.db
scp /root/dump.db server3:/root/
server3
:
scp -r root@172.25.72.2:/usr/local/mysql /usr/local/
mkdir -p /data/mysql
useradd -M -s /sbin/nologin mysql
chown mysql.mysql /data/mysql
vim ~/.bash_profile
source ~/.bash_profile
vim /etc/my.cnf
source ~/.bash_profile
mysqld --initialize --user=mysql ##初始化
/etc/init.d/mysqld start
mysql_secure_installation
mysql -uroot -pwestos -e "create database rtt;" ##为了导入
mysql -p rtt < dump.db ##导入时,若报gtid相关错误,应关闭gtid后重新备份及导入
mysql -p
mysql> select * from rtt.zy;
mysql> change master to master_host='172.25.72.2', master_user='repl', master_password='westos', master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;
当执行
mysql> show slave status\G;
时,出现以下情况:
是因为主从两端日志不一致导致,
reset master|slave;
即可解决。
2、GTID半同步复制
主------------------>从
server1
server2
server1
:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
server2
:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
server1
:
mysql> set global rpl_semi_sync_master_enabled = 1;
server2
:
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> stop slave io_thread;
mysql> start slave io_thread;
测验
:
当主从两边线程都开启
时:
server1
:
mysql> insert into rtt.zy values ('user11','111');
mysql> show status like 'rpl%';
server2
:
mysql> select * from zy;
当关闭
server2的线程时:
server2
:
mysql> stop slave io_thread;
server1
:
mysql> insert into rtt.zy values ('user12','111');
mysql> show status like 'rpl%';
再次开启
server2线程
server2
:
mysql> start slave io_thread;
server1
:
mysql> insert into rtt.zy values ('user13','111');
mysql> show status like 'rpl%';
server2
:
mysql> select * from rtt.zy;
虽然当建立了user12向server2传递显示失败
,但在server2
端依然可以查到
3、慢查询
设置sql延迟复制
server2:
mysql -p
mysql> stop slave;
mysql> change master to master_delay = 20 ; ##延迟时间 20s
mysql> start slave;
mysql> show slave status \G;
设定慢查询
vim /etc/my.cnf
mysql> show processlist;
mysql> show variables like ‘slow%’; ##查看慢查询有没有被打开
mysql> set global slow_query_log=1; ##开启慢查询 ‘1’为开启 ‘0’为关闭
mysql> show variables like ‘slow%’;
mysql> select sleep(10); ##设置休眠10s
mysql> show variables like ‘long%’; ##
4、MySQL组复制
多主模式
配置
主---------->主------------->主
server1 server2 server3
server1
:
/etc/init.d/mysqld stop
rm -fr /data/mysql/*
vim /etc/my.cnf
/etc/init.d/mysqld restart
mysqld --initialize-insecure --user=mysql
/etc/init.d/mysqld start
mysql
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT REPLICATION SLAVE ON . TO rpl_user@’%’;
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user’, MASTER_PASSWORD=‘password’ FOR CHANNEL ‘group_replication_recovery’;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> START GROUP_REPLICATION;
server2
:(同一)
/etc/init.d/mysqld stop
rm -fr /data/mysql/*
vim /etc/my.cnf
mysqld --initialize-insecure --user=mysql
/etc/init.d/mysqld start
mysql
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT REPLICATION SLAVE ON . TO rpl_user@’%’;
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user’, MASTER_PASSWORD=‘password’ FOR CHANNEL ‘group_replication_recovery’;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> START GROUP_REPLICATION;
server3
:(同二)
/etc/init.d/mysqld stop
rm -fr /data/mysql/*
vim /etc/my.cnf
mysqld --initialize-insecure --user=mysql
/etc/init.d/mysqld start
mysql
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT REPLICATION SLAVE ON . TO rpl_user@’%’;
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user’, MASTER_PASSWORD=‘password’ FOR CHANNEL ‘group_replication_recovery’;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> START GROUP_REPLICATION;
检验:
server1:
mysql> select * from performance_schema.replication_group_members;
5、mysql路由器
设定server4为路由器
:
真机
中:
qemu-img create -f qcow2 -b server.qcow2 server4 ##建立镜像server4
设定网桥:
hostnamectl set-hostname server4
vim /etc/sysconfig/network-scripts/ifcfg-ens3 ##更改ip
systemctl restart network
真机中
:
iptables -t nat -I POSTROUTING -s 172.25.72.0/24 -j MASQUERADE ##地址伪装
server4
:
reboot ##重新开启地址伪装才生效
ssh server4
lftp 172.25.254.250
lftp 172.25.254.250:/pub/docs/mysql> get mysql-router-community-8.0.21-1.el7.x86_64.rpm
rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
cd /etc/mysqlrouter/
ls
vim mysqlrouter.conf
systemctl start mysqlrouter.service
netstat -antlp
yum install -y lsof
server1
:
mysql> grant all on rtt.* to rtt@’%’ identified by ‘westos’;
真机中
:
yum install -y mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
##仓库里有
mysql -h 172.25.72.4 -P 7001 -u rtt -p
测试
:
server4
:
netstat -antlp
lsof -i :7001
真机
:
mysql -h 172.25.72.4 -P 7001 -u rtt -p
server1
:
yum install -y lsof
netstat -antlp
lsof -i :3306
server2
:
yum install -y lsof
netstat -antlp
lsof -i :3306
server3
:
yum install -y lsof
netstat -antlp
lsof -i :3306