MySQL

一、 通过编译、二进制安装MySQL5.7

编译安装

参考：https://cloud.tencent.com/developer/article/1602574

进入网址：https://downloads.mysql.com/archives/community/选择相应版本，下载源码包，需要注意的是mysql5.7 编译安装需要boost 库

可用命令直接下载

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37.tar.gz
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.37.tar.gz

1.安装相关依赖包

yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel \
libarchive-devel boost-devel ncurses-devel gnutls-devel libxml2-devel \
openssl-devel libevent-devel libaio-devel perl-Data-Dumper

2.做准备用户和数据目录

useradd -r -s /sbin/nologin -d /data/mysql mysql

3.准备数据库目录

mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql

4.源码编译安装

4.1编译安装说明

利用cmake编译,而利用传统方法，cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译

编译选项参考:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

4.2解压缩源码包

tar -xvf mysql-5.7.37.tar.gz -C /usr/local/src
tar -xvf mysql-boost-5.7.37.tar.gz -C /usr/local/src
chown -R root:root /usr/local/src/mysql-5.7.37

4.3源码编译安装 MySQL

cd /usr/local/src/mysql-5.7.37
cmake . \
-DCMAKE_INSTALL_PREFIX=/apps/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_BOOST=boost
make && make install

说明：

make -j 4 && make install
#-j参数作用：编译时会占用大量的系统资源，可以通过-j参数指定多个编译命令进行并行编译来提高速度,使用以下命令查看系统CPU核数
[root@shichu mysql-5.7.37]# cat /proc/cpuinfo | grep processor |wc -l
4

4.4准备环境变量

echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

4.5生成数据库文件

cd /apps/mysql/
bin/mysql_install_db --datadir=/data/mysql/ --user=mysql

4.6准备配置文件

配置/etc/my.cnf文件
注意：5.7版本没有模板文件/application/mysql/support-files/my-default.cnf，可根据需要自行添加
[root@shichu mysql]# cat /etc/my.cnf
[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
user = mysql
basedir = /apps/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
#sql_mode='ONLY_FULL_GROUP_BY'
log_error = /data/mysql/mysql.log
!includedir /etc/my.cnf.d
 
[client]
port = 3306
socket = /data/mysql/mysql.sock
 

4.7准备启动脚本,并启动服务

cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
systemctl start mysqld
systemctl status mysqld

4.8查看默认密码

[root@shichu ~]# cat /root/.mysql_secret 
# Password set for user 'root@localhost' at 2022-05-09 23:54:52 
4<>n7eGqY8(e

4.9修改密码

mysql -uroot -p`sed -n 2p /root/.mysql_secret`
# 查看 mysql 初始的密码策略
mysql> show variables like 'validate_password%';  
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
 
# 根据需要修改设置密码的验证强度等级
mysql> set global validate_password_policy=low;
Query OK, 0 rows affected (0.00 sec)
 
# 设置密码长度
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
 
# 设置简单密码
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
或者
[root@shichu ~]# mysqladmin -uroot -p`sed -n 2p /root/.mysql_secret` password 123456

关于 mysql 密码策略相关参数说明

注：在默认密码的长度最小值为 4 ，由 大/小写字母各一个 + 阿拉伯数字一个 + 特殊字符一个，
只要设置密码的长度小于 3 ，都将自动设值为 4 ，
 
关于 mysql 密码策略相关参数；
1）、validate_password_length 固定密码的总长度；
2）、validate_password_dictionary_file 指定密码验证的文件路径；
3）、validate_password_mixed_case_count 整个密码中至少要包含大/小写字母的总个数；
4）、validate_password_number_count 整个密码中至少要包含阿拉伯数字的个数；
5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；
    关于 validate_password_policy 的取值：
    0/LOW：只验证长度；
    1/MEDIUM：验证长度、数字、大小写、特殊字符；
    2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；
6）、validate_password_special_char_count 整个密码中至少要包含特殊字符的个数；

4.10测试登录

[root@shichu ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.37 Source distribution
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
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> 

4.11安全初始化（可选）

安装完mysql-server 会提示可以运行mysql_secure_installation。运行mysql_secure_installation会执行几个设置：
  a)为root用户设置密码
  b)删除匿名账号
  c)取消root用户远程登录
  d)删除test库和对test库的访问权限
  e)刷新授权表使修改生效
详细步骤请参看下面的命令:

[root@shichu ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):<–初次运行直接回车
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] <– 是否删除匿名用户,生产环境建议删除，所以直接回车
… Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] <– 是否删除test数据库,直接回车
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

二进制安装

1.安装相关包

yum install libaio numactl-libs -y

2.添加用户和组

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

3.准备程序文件

下载mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz文件至/usr/local目录下

cd /usr/local
tar xvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
chown -R root:root mysql

4.准备环境变量

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

5.准备配置文件

mkdir -p /data/mysql
cp /etc/my.cnf{,.bak}
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
 
[client]
socket=/data/mysql/mysql.sock
 

初始化数据库文件并提取root密码

方式1: 生成随机密码

mysqld --initialize --user=mysql --datadir=/data/mysql

查看随机密码：

[root@shichu /]# grep password /data/mysql/mysql.log
2022-05-04T19:49:50.840907Z 1 [Note] A temporary password is generated for root@localhost: d9TgYC5>/C,k

提前随机密码：

[root@shichu /]# awk '/temporary password/{print $NF}' /data/mysql/mysql.log
d9TgYC5>/C,k

方式2：生成 root 空密码

mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

6.准备服务脚本和启动

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
systemctl start mysqld

7.修改口令

#修改前面生成的随机密码为指定密码
mysqladmin -uroot -p`awk '/temporary password/{print $NF}' /data/mysql/mysql.log` password 123456
 
#修改前面生成的空密码为指定密码
mysqladmin -uroot   password 123456

8.测试登录

[root@shichu mysql]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
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> 

二、二进制安装mariadb10.4

参考：https://www.cnblogs.com/fb010001/p/12037655.html

1.添加用户和组

groupadd mysql
useradd -r -g mysql -d /data/mysql -s /sbin/nologin mysql

2.准备数据目录

mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql

3.下载mariadb 二进制tar包

https://archive.mariadb.org/mariadb-10.4.24/bintar-linux-systemd-x86_64/mariadb-10.4.24-linux-systemd-x86_64.tar.gz

解压tar包指/usr/local目录下

tar xvf mariadb-10.4.24-linux-systemd-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -s mariadb-10.4.24-linux-systemd-x86_64 mysql
chown -R root:mysql /usr/local/mysql

4.准备配置文件

mkdir -p /etc/mysql
cp /etc/my.cnf /etc/mysql/my.cnf
 
vim /etc/mysql/my.cnf
[mysqld]
datadir=/data/mysql
innodb_file_per-table=on
skip_name_resolve=on
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/mariadb.log
pid-file=/data/mysql/mariadb.pid
 
!includedir /etc/my.cnf.d
 
 
或者
sed -ri '/datadir=//s@(. *=).* @\1/data/mysql@' /etc/mysql/my.cnf #修改配置文件，指定数据库储存路径
sed -ri '/datadir/a\innodb_file_per-table=on\nskip_name_resolve=on' /etc/mysql/my.cnf #设置每个表独立文件 和 禁用主机名解析
 

5.创建数据库文件

/usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql --user=mysql

6.准备服务脚本和启动

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld

7.准备环境变量

echo 'PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

8.启动数据库

systemctl start mysqld

9.MariaDB安全配置（初始化）

#设置root口令、禁用匿名登陆、禁用远程主机登陆、删除test数据库，并立即生效(根据提示操作)。
ln -s /data/mysql/mysql.sock /tmp
/usr/local/mysql/bin/mysql_secure_installation

# 输入root(mysql)的密码。默认没有，直接回车
Enter current password for root (enter for none): 
# 是否切换到unix套接字身份验证[Y/n]
Switch to unix_socket authentication [Y/n] n
# 是否设置root密码
Change the root password? [Y/n] y
# 如果选Y，就输入2次密码
  New password:
  Re-enter new password:
# 是否删除匿名用户?(就是空用户)，建议删除
Remove anonymous users? [Y/n] y
# 是否不允许远程root登录 y
Disallow root login remotely? [Y/n] y
# 是否删除test数据库
Remove test database and access to it? [Y/n] y
# 是否加载权限使之生效
Reload privilege tables now? [Y/n] y

10.测试登录

[root@shichu mysql]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.4.24-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
 
MariaDB [(none)]> 
 

三、导入hellodb.sql生成数据库

mysql> source ~/hellodb_innodb.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> use hellodb
Database changed

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

mysql> select name,age from students where age>25 and gender='m';
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.00 sec)

(2) 以ClassID为分组依据，显示每组的平均年龄

mysql> select classid, avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
8 rows in set (0.00 sec)

(3) 显示第2题中平均年龄大于30的分组及平均年龄

mysql> select classid, avg(age) from students group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       2 |  36.0000 |
|       5 |  46.0000 |
+---------+----------+
3 rows in set (0.00 sec)

或

mysql> select classid, avg(age) as 平均年龄 from students group by classid having 平均年龄>30;
+---------+--------------+
| classid | 平均年龄     |
+---------+--------------+
|    NULL |      63.5000 |
|       2 |      36.0000 |
|       5 |      46.0000 |
+---------+--------------+
3 rows in set (0.00 sec)

(4) 显示以L开头的名字的同学的信息

mysql> select * from students where name like 'l%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

四、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

10.0.0.23 mysql数据库授权magedu用户：

mysql> create user magedu@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

192.168.1.10可连接10.0.0.23 mysql数据库

[root@shichu ~]# mysql -umagedu -h10.0.0.23
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.33 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
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> 

五、主从复制及主主复制的实现

主从复制

一主两从架构

master节点

#安装mariadb
yum install mariadb-server -y
#修改配置
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=15
log-bin
#重启数据库
systemctl restart mariadb
 
#查看二进制文件和位置
[root@master ~]# mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> 
# 创建并授权复制用户
MariaDB [(none)]> create user 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

slave节点

#安装mariadb
yum install mariadb-server -y
#配置文件，设置只读
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=16  # server-id=17
read-only  
 
#重启数据库
systemctl restart mariadb
[root@slave1 ~]# mysql
# 根据实际信息修改范例
MariaDB [(none)]> help change master to
  CHANGE MASTER TO
  MASTER_HOST='10.0.0.15',	#master IP
  MASTER_USER='repluser',	#master创建的授权用户名
  MASTER_PASSWORD='',		#密码：空密码
  MASTER_PORT=3306,		#端口
  MASTER_LOG_FILE='mariadb-bin.000001',	#二进制文件Log_name
  MASTER_LOG_POS=245,			#二进制文件File_size
  MASTER_CONNECT_RETRY=10;
  
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.15',MASTER_USER='repluser',\
MASTER_USER='repluser',MASTER_PASSWORD='',MASTER_PORT=3306,\
MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245,MASTER_CONNECT_RETRY=10;
 
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.15',MASTER_USER='repluser',\
    -> MASTER_USER='repluser',MASTER_PASSWORD='',MASTER_PORT=3306,\
    -> MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245,MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show slave status\G
 
 

验证

master导入hellodb
[root@master ~]# mysql < hellodb_innodb.sql 
[root@master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 92
Server version: 5.5.68-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 

slave节点查看

slave1:
[root@slave1 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
 
 
slave2:
[root@slave2 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
 

主主复制

两主架构

操作步骤

1.master1

yum install mariadb-server -y
 
vim /etc/my.cnf.d/mariadb-server.cnf
[root@shichu ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=10
log-bin
auto_increment_offset=1
auto_increment_increment=2
 
[root@shichu ~]# systemctl start mariadb
[root@shichu ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

2.master2

yum install mariadb-server -y
 
vim /etc/my.cnf.d/mariadb-server.cnf
[root@shichu ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
auto_increment_offset=2
auto_increment_increment=2
 
[root@shichu ~]# systemctl start mariadb
[root@shichu ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.10',MASTER_USER='repluser',\
    -> MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,\
    -> MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245,MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

3.master1

#注意master2 中二进制文件的值
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.18',MASTER_USER='repluser',\
    -> MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,\
    -> MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245,MASTER_CONNECT_RETRY=10;
 
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

验证

master1：创建表t1

MariaDB [(none)]> use test
Database changed
MariaDB [test]> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.00 sec)

master2：在t1表中添加xiaoming

MariaDB [(none)]> use test
MariaDB [test]> insert t1(name) values('xiaoming');
Query OK, 1 row affected (0.00 sec)

master1：在t1表中添加Alice，可查询到xiaoming、Alice信息

MariaDB [test]> insert t1 (name) values('Alice');
 
MariaDB [test]> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  2 | xiaoming |
|  3 | Alice    |
+----+----------+
2 rows in set (0.00 sec)

master2：可查询到xiaoming、Alice信息

MariaDB [test]> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  2 | xiaoming |
|  3 | Alice    |
+----+----------+
2 rows in set (0.00 sec)
 
MariaDB [test]> 

六、xtrabackup实现全量+增量+binlog恢复库

准备环境：

操作系统：CentOS 7 
数据库：mariadb 5.5
原主机：10.0.0.10
目标主机：10.0.0.17

备份

#提前开启log-bin二进制日志记录
#将下载好的percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm安装包拷贝到该主机上
#安装xtrabackup包
yum install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm -y
mkdir /backup

1.数据库全量备份

xtrabackup -uroot -p123456 --backup --target-dir=/backup/base

2.第一次修改数据

第一次增量备份
xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

查看xtrabackup相关文件

3.第二次修改数据

第二次增量备份

xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

查看xtrabackup相关文件

4.复制备份目录至目标主机

scp -r /backup/ 10.0.0.17:/backup

还原

#提前开启log-bin二进制日志记录
#将下载好的percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm安装包拷贝到该主机上
#安装xtrabackup包
yum install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm -y
 
 
#预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#合并第1次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
#合并第2次增量备份到完全备份
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
 
#停止mariadb服务，清空数据库目录
systemctl stop mariadb
rm -rf /var/lib/mysql/*
#复制到数据库目录
xtrabackup --copy-back --target-dir=/backup/base
#还原属性
chown -R mysql:mysql /var/lib/mysq
#启动服务
systemctl start mariadb

目标主机数据库查看

七、MyCAT实现MySQL读写分离

master

[root@shichu ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
server-id=18
log-bin
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
 
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
 
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
 
 
[root@shichu ~]# systemctl start mariadb
 
 
 
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      475 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> 
 

slave

[root@shichu ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
server-id=19
log-bin
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
 
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
 
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
 

change master to master_host='10.0.0.18',master_user='repluser',master_password='replpass',master_log_file='mariadb-bin.000001',master_log_pos=475;

主从架构详细配置说明可参考主从复制配置部分。

mycat

注意：如果内存太小,会导致mycat无法启动

1. 将Mycat安装包Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz拷贝至服务器

yum install java mariadb -y
tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
 
echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh

2. 修改/apps/mycat/conf/server.xml文件

   #修改连接mycat虚拟数据库的密码
       <user name="root" defaultAccount="true">
           <property name="password">mycat</property>
           <property name="schemas">TESTDB</property>
           <property name="defaultSchema">TESTDB</property>
       </user>
    
   #取消以下注释，修改serverPort为3306
   <property name="serverPort">3306</property> <property name="managerPort">9066</property>                      
   <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
   <property name="dataNodeIdleCheckPeriod">300000</property>
   <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
   <property name="handleDistributedTransactions">0</property>
    

3. 修改/apps/mycat/conf/schema.xml文件，实现读写分离

   #参考以下修改相关IP
   <?xml version="1.0"?>
   <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
   <mycat:schema xmlns:mycat="http://io.mycat/">                                                                             
       <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
       </schema>
       <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
       <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
           <heartbeat>select user()</heartbeat>
           <writeHost host="host1" url="10.0.0.18:3306" user="root"
                      password="123456">
               <readHost host="host2" url="10.0.0.19:3306" user="root" password="123456"/>
           </writeHost>
       </dataHost>
   </mycat:schema>            

4. 启动mycat

   mycat start

5. 查看启动日志

   tail -f /apps/mycat/logs/wrapper.log

   

验证

0. 准备条件

   master创建用户并对mycat授权

   [root@master ~]# mysql -uroot
   mysql> create database mycat;
   mysql>GRANT ALL ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY '123456' ;
   mysql> flush privileges;

   在主和从服务器分别启用通用日志，查看读写分离

   #master服务器
   vim /etc/my.cnf.d/mariadb-server.cnf
   [mysqld]
   general_log=ON  
    
   [root@master ~]# systemctl restart mariadb
    
   #查看通用日志
   [root@master ~]# tail -f /var/lib/mysql/master.log 

   #slave服务器
   vim /etc/my.cnf.d/mariadb-server.cnf
   [mysqld]
   general_log=ON  
    
   [root@master ~]# systemctl restart mariadb
    
   #查看通用日志
   [root@master ~]# tail -f /var/lib/mysql/slave.log 

1. client连接Mycat服务器

[root@client ~]# mysql -uroot -pmycat -h10.0.0.17
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
 

可正常连接并看到TESTDB虚拟数据库

2. client创建表，查看通用日志显示是在master节点操作的

   MySQL [TESTDB]> create table tt(id int);
   Query OK, 0 rows affected (0.02 sec)

   

3. client查询操作，显示连接的是slave节点

MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave      |
+------------+
1 row in set (0.00 sec)
 
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          19 |
+-------------+
1 row in set (0.00 sec)
 
MySQL [TESTDB]> 
 

4. 停止slave节点，Mycat自动调度到master节点

MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          18 |
+-------------+
1 row in set (0.00 sec)
 
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| master     |
+------------+
1 row in set (0.00 sec)
 
MySQL [TESTDB]>