1.源码编译安装mysql数据库
取出安装包
安装成功后进入目录,安装cmake
yum install cmake -y
按照定制功能源码编译mysql
首先安装依赖性文件,也可边configure边安装
yum install gcc-c++ -y
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_0900_ai_ci -DWITH_BOOST=/root/mysql-5.7.31/boost/boost_1_59_0
结果如上
make(耗费时间较长)
结果:
make install
结果:
mysql源码编译安装完成
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
cd
vim .bash_profile
source .bash_profile
useradd -M -d /data/mysql -s /sbin/nologin mysql
mkdir -p /data/mysql ; chown mysql.mysql /data/mysql
chmod 750 /data/mysql
初始化数据库,生成随即密码,初始化完成后会在数据库存储目录生成文件
mysqld --initialize --user=mysql
开启mysql服务
/etc/init.d/mysqld start
启动时若出现需要upgrade server.pid的报错则
vim /etc/my.cnf
[mysqld_safe]
log-error=/data/mysql/mysql.log
pid-file=/var/run/mysql.pid
安全初始化mysql
mysqld --initialize --user=mysql
mysql_secure_installation
测试:
mysql -p
2. 安装phpmyadmin
将安装包移动到nginx发布目录中并解压
mv phpMyAdmin-5.0.2-all-languages.zip /usr/local/nginx/html
yum install -y unzip
cd /usr/local/nginx/html/
unzip phpMyAdmin-5.0.2-all-languages.zip
vim /usr/local/nginx/conf/nginx.conf
nginx -t 检测语法
nginx -s reload 刷新nginx服务
mv phpMyAdmin-5.0.2-all-languages phpadmin 改名
网页访问http://172.25.76.1/phpadmin/
套接字相关设置
vim /usr/local/php/etc/php.ini
1056 pdo_mysql.default_socket=/data/mysql/mysql.sock
1166 mysqli.default_socket = /data/mysql/mysql.sock
systemctl reload php-fpm
网页访问后权限不够
chmod 755 /data/mysql
重新访问
phpmyadmin与mysql结合成功
3.mysql主从复制
原理:
server1:
scp -r /usr/local/mysql/ server2:/usr/local/
scp /etc/my.cnf server2:/etc/
scp /etc/init.d/mysqld server2:/etc/init.d/
vim /etc/my.cnf
重启mysql数据库
/etc/init.d/mysqld restart
查看主机状态
show master status;
创建mysql用户并授权
CREATE USER 'repl'@'%' IDENTIFIED BY 'westos' #创建用户repl可以通过任何方式登陆,密码为westos
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #授权
server2:
vim .bash_profile
source .bash_profile
mkdir /data/mysql -p
useradd -M -d /data/mysql -s /sbin/nologin mysql
chown mysql.mysql /data/mysql/
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql_secure_installation
编辑mysql主配置文件,给定主机server id
vim /etc/my.cnf ,添加设置从server id
重启mysql
/etc/init.d/mysqld restart
若之前的mysql以存在数据库则需先备份
server1: mysqldump -pwestos westos > dump.sql scp
dump.sql server2:
server2: mysqladmin -pwestos create westos
mysql -pwestos westos < dump.sql
mysql 导入报错(ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can
only be set when @@GLOBAL.GTID_E)时
mysql> reset master; 清空master日志
Query OK, 0rows affected (0.01 sec)mysql> quit
Bye
测试:
server2:
连接主机ip的mysql
change master to master_host=‘172.25.76.1’, master_user=‘repl’, master_password=‘westos’, master_log_file=‘mysql-bin.000001’ ,master_log_pos=154;
mysql -h 172.25.9.1 -u repl -p
配置连接参数:
server1 :
创建库表
create database westos; #创建库
mysql> create table westos.user( #创建表
-> username varchar(10));
INSERT INTO westos.user VALUES('user1'); #插入数据
select * from user; #查看数据
开启slave并查看状态
start slave;
show slave status\G;
若出现两个yes 则成功
主机master中的操作已经成功复制到从机slave中
4.Gtid实现主从复制
一主一从
操作:
master主机通过主配置文件打开gtid模式
vim /etc/my.cnf
重启mysql
/etc/init.d/mysqld restart
slave主机也编辑主配置文件打开gtid模式
/etc/init.d/mysqld restart 重启服务
slave主机关闭slave,配置连接参数
mysql -p
mysql> stop slave;
mysql> change master to master_host='172.25.76.1',master_user='repl',master_password='westos',master_auto_position=1;
mysql> start slave;
测试:
master:
slave:
select * from westos.westos1;
一主多从
通常使用与读作用大于写作用
通过server3连接server2
server1:
mysqldump -pwestos westos >dump.sql
scp dump.sql server3:
scp -r /usr/local/mysql/ server3:/usr/local/
scp /etc/my.cnf server3:/etc/
scp /etc/init.d/mysqld server3:/etc/init.d/
server2:
vim /etc/my.cnf,使得二进制文件能够被读取并且开启slave更新开关
重启服务
/etc/init.d/mysqld restart
授权repl用户用于slave服务
grant replication slave on . to repl@’%’ identified by ‘westos’;
server3:
vim .bash-profile
source .bash_profile
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql_secure_installation
mysql -pwestos
mysql> reset master
mysql -pwestos westos < dump.sql
vim /etc/my.cnf
/etc/init.d/mysqld restart
mysql -pwestos
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.25.76.2', master_user='repl', master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
测试
server1:
server2与server3:
select * from westos.westos1;
一主二从成功
5. mysql半同步复制
server1:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装master模块
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_enabled = 1; #启动模块
Query OK, 0 rows affected (0.00 sec)
show variables like 'rpl%'; #查看复制
server2:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
server3:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
当我们在server1中 添加一个数据
INSERT INTO westos.westos1 VALUES (‘lcfmy’,‘331’);
show status like ‘rpl%’; 查看半同步复制工作工作状态后会发现高光的地方变成1
且server23 复制成功
若关闭io-thread 则默认等待最长响应时间10s过后 自动添加
但server2 3 不会添加数据 需要重新打开io端才可以实现。
并且 show status like ‘rpl%’; 后 以下三个变量会加1
| Rpl_semi_sync_master_net_waits
| Rpl_semi_sync_master_tx_waits
| Rpl_semi_sync_master_yes_tx
6.mysql服务:
延迟复制
进入slave端的mysql
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 30;
mysql> start slave;
即当有数据需要复制时候,会等待30再复制。
并行复制(MTS)
慢查询
7.mysql组复制
server1:
初始化mysql
先关闭运行的mysql
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
collation-server=utf8mb4_general_ci
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="server1:33061"
group_replication_group_seeds="server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.76.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
[mysqld_safe]
log-error=/data/mysql/mysql.log
pid-file=/var/run/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
cd /data/mysql/
rm -rf *
mysqld --initialize-insecure --user=mysql
/etc/init.d/mysqld start
[root@server1 ~]# mysql
mysql: Collation 'utf8mb4_0900_ai_ci' is not a compiled collation and is not specified in the '/usr/local/mysql/share/charsets/Index.xml' file
mysql: Collation 'utf8mb4_0900_ai_ci' is not a compiled collation and is not specified in the '/usr/local/mysql/share/charsets/Index.xml' file
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0; ###关闭二进制文件写入,创建用户并且授权,完成后代开二进制文件写入。
Query OK, 0 rows affected (0.00 sec)
mysql> create user rpl_user@'%' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='rpl_user', master_password='westos' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON; ##master主机启动组复制必备
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.06 sec)
mysql> select * from performance_schema.replication_group_members; ##查看相关模块状态,只有ONLINE才能实现组复制。
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 951ae552-1379-11ec-ba65-525400fa3e23 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
server2:
server3:
先关闭mysql
并初始化mysql
步骤同server1(只需将配置文件中的 serverid 改成对应的数字)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos'
-> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> START GROUP_REPLICATION; ##开启模块
Query OK, 0 rows affected (5.78 sec)
在server1中查看
SELECT * FROM performance_schema.replication_group_members; # 查看
测试:
server1中创建库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t1(c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values (1,'luis');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | luis |
+----+------+
1 row in set (0.00 sec)
在server23可查看
8. mysql路由器
打开一台虚拟机server4
安装查询模块
rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
安装成功后,配置主配置文件
vim /etc/mysqlrouter/mysqlrouter.conf
[logger]
level = INFO
# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations=172.25.76.1:3306,172.25.76.2:3306,172.25.76.3:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations=172.25.76.3:3306,172.25.76.2:3306,172.25.76.1:3306
routing_strategy = first-available
systemctl start mysqlrouter.service
netstat -antlp # 查看7001端口和7001端口是否已经打开
mysql> grant all on test.* to lcf@'%' identified by 'westos'; ####server1中授权用户lcf用于查看test库的所有表文件
Query OK, 0 rows affected, 1 warning (0.01 sec)
真实主机连接server4 通过server4 连接7001 和 7002端口
当访问7001
[root@foundation76 images]# mysql -h 172.25.76.4 -P 7001 -u lcf -p
Enter password:
server3
当访问7002
server3
/etc/init.d/mysqld start
当遇到ERROR! The server quit without updating PID file (/data/mysql/server1.pid).
这种报错时 我们通过ps xa| grep mysql 找到与pid相关的进程并将他kill掉后在尝试即可成功登陆