linux_6
一、编译安装MySQL
数据库版本 MySQL-5.7.37
下载mysql-5.7.37
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.37.tar.gz
下载boost库
http://www.sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
#1、安装相关依赖包
[root@centos7-01 local]# yum -y install gcc gcc-c++ cmake bison bison-devel
[root@centos7-01 local]# yum -y install zlib-devel libcurl-devel libarchive-devel
[root@centos7-01 local]# yum -y install boost-devel ncurses-devel gnutls-devel libxml2-devel
[root@centos7-01 local]# yum -y install openssl-devel libevent-devel libaio-devel perl-Data-Dumper
#2、创建文件夹
[root@centos7-01 local]# mkdir /usr/local/boost
#3、上传boost_1_59_0.tar.gz，解压缩并授权
[root@centos7-01 local]# cd /usr/local/boost/
[root@centos7-01 boost]# tar zxf boost_1_59_0.tar.gz
[root@centos7-01 boost]# chmod 755 /usr/local/boost/boost_1_59_0
[root@centos7-01 boost]# chown -R root:root /usr/local/boost/
#4、创建用户
[root@centos7-01 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql
#5、上传boost_1_59_0.tar.gz，mysql-5.7.37.tar.gz解压缩
[root@centos7-01 ~]# tar xvf mysql-5.7.37.tar.gz -C /usr/local/src
#6、创建mysql目录并授权
[root@centos7-01 mysql-5.7.37]# mkdir -p /data/mysql
[root@centos7-01 mysql-5.7.37]# chown -R mysql:mysql /data/mysql/
#7、源码编译安装
[root@centos7-01 mysql-5.7.37]# cd /usr/local/src/mysql-5.7.37/
[root@centos7-01 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\
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost
#8、#等待执行结束后，开始安装，需要慢慢等待
[root@centos7-01 mysql-5.7.37]# make && make install
#9、修改my.cnf文件
[root@centos7-01 mysql-5.7.37]# vim /data/mysql/my.cnf
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/apps/mysql
datadir=/data/mysql
tmpdir=/tmp
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
#10、准备环境变量
[root@centos7-01 mysql-5.7.37]# echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7-01 mysql-5.7.37]# . /etc/profile.d/mysql.sh
#11、生成数据文件
[root@centos7-01 ~]# cd /apps/mysql/
[root@centos7-01 mysql]# mysqld --defaults-file=/etc/my.cnf --initialize
#12、准备启动脚本,并启动服务
[root@centos7-01 mysql]# cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7-01 mysql]# chkconfig --add mysqld
[root@centos7-01 mysql]# service mysqld start
#13、查看有没有3306端口
[root@centos7-01 mysql]# ss -ntl
#14、查看随机密码
[root@centos7-01 mysql]# cd /data/mysql/
[root@centos7-01 mysql]# grep password mysqld.log
2022-05-11T17:00:10.599477Z 1 [Note] A temporary password is generated for root@localhost: gqw-Tw_sv3pj
#15、登录数据库
[root@centos7-01 mysql]# mysql -uroot -pgqw-Tw_sv3pj
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: Collation 'utf8_general_ci-DDOWNLOAD_BOOST=1' is not a compiled collation and is not specified in the '/apps/mysql/share/charsets/Index.xml' file
mysql: Collation 'utf8_general_ci-DDOWNLOAD_BOOST=1' is not a compiled collation and is not specified in the '/apps/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.37
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.
#16、修改数据库密码
mysql> alter user 'root'@'localhost' identified by "magedu";
Query OK, 0 rows affected (0.00 sec)
#17、重新登录
mysql> exit
Bye
[root@centos7-01 mysql]# mysql -uroot -pmagedu
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)二、二进制安装MySQL
数据库版本 MySQL-5.7.37
#1、安装依赖包
[root@centos7-02 ]# yum install -y libaio*
#2、创建用户组
[root@centos7-02 ]# useradd -r -s /sbin/nologin -d /data/mysql mysql
#3、下载数据库文件
图片
#3、上传mysql
[root@centos7-02 ]# cd /usr/local/
[root@centos7-02 local]# ls
bin etc games include lib lib64 libexec mysql-5.7.37-el7-x86_64.tar.gz sbin share src
#4、将数据库文件解压缩
[root@centos7-02 local]# mkdir mysql
[root@centos7-02 local]# tar zxvf mysql-5.7.37-el7-x86_64.tar.gz
[root@centos7-02 local]# ln -s mysql-5.7.37-el7-x86_64/ mysql
[root@centos7-02 local]# chown -R root.root /usr/local/mysql/
#5、创建数据库文件夹
[root@centos7-02 local]# mkdir /data/mysql -pv
[root@centos7-02 local]# chown mysql.mysql /data/mysql
#6、准备环境变量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
#7、数据库初始化。生成随机密码
[root@centos7-02 mysql]#./bin/mysqld --initialize --user=mysql --datadir=/data/mysql
[root@centos7-02 mysql]# cd /data/mysql/
[root@centos7-02 mysql]# grep password mysql.log
2022-05-11T22:07:15.371889Z 1 [Note] A temporary password is generated for root@localhost: .Je#Y85Squcc
#8、配置my.cnf文件
[root@centos7-02 local]# 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
#9、准备服务脚本和启动
[root@centos7-02 mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7-02 mysql]# chkconfig --add mysqld
[root@centos7-02 mysql]# service mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.log'.
SUCCESS!
#10、查看端口是否启动
[root@centos7-02 mysql]# ss -ntl
#11、登录数据库
[root@centos7-02 mysql]# mysql -uroot -p.Je#Y85Squcc
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 2
Server version: 5.7.37
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>
#12、修改数据库密码
mysql> alter user 'root'@'localhost' identified by "magedu";
Query OK, 0 rows affected (0.00 sec)
#13、重新登录
mysql> exit
Bye
[root@centos7-01 mysql]# mysql -uroot -pmagedu
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
数据库版本
#上传mariadb-10.4.22-linux-glibc_214-x86_64.tar.gz
[root@centos7-01 ~]# ls
anaconda-ks.cfg mariadb-10.4.24-linux-glibc_214-x86_64.tar.gz
#1、安装相关包
[root@centos7-01 ~]# yum -y install libaio numactl-libs
#2、创建用户和组
[root@centos7-01 ~]# groupadd mysql
[root@centos7-01 ~]# useradd -r -g mysql -s /bin/false mysql
#3、解压缩准备程序文件
[root@centos7-01 ~]# tar xfv mariadb-10.4.24-linux-glibc_214-x86_64.tar.gz -C /usr/local
[root@centos7-01 ~]# cd /usr/local/
#4、软连接
[root@centos7-01 local]# ln -s mariadb-10.4.24-linux-glibc_214-x86_64/ mysql
#5、准备环境变量
[root@centos7-01 local]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7-01 local]# . /etc/profile.d/mysql.sh
#6、创建数据库目录，修改文件夹权限
[root@centos7-01 local]# mkdir /data/mysql -pv
#7、准备配置文件
[root@centos7-01 local]# cd /usr/local/mysql/
[root@centos7-01 mysql]# cp /etc/my.cnf{,.bak}
[root@centos7-01 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/var/lib/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
[mysqld_safe]
log-error=/data/mysql/mysql.log
pid-file=/var/run/mariadb/mariadb.pid
# include all files from the config directory
!includedir /etc/my.cnf.d
#8、创建配置文件中指定的路径并修改权限
[root@centos7-01 mysql]# mkdir /var/lib/mysql
[root@centos7-01 mysql]# chown -R mysql:mysql /var/lib/mysql/
[root@centos7-01 mysql]# touch /var/lib/mysql/mysql.sock
[root@centos7-01 mysql]# chmod guo+wr /var/lib/mysql/mysql.sock
[root@centos7-01 mysql]# ll /var/lib/mysql
total 0
-rw-rw-rw- 1 root root 0 May 10 20:55 mysql.sock
[root@centos7-01 mysql]# touch /data/mysql/mysql.log
[root@centos7-01 mysql]# ll /data/mysql/
total 0
-rw-r--r-- 1 root root 0 May 10 20:57 mysql.log
[root@centos7-01 mysql]# chmod guo+rw /data/mysql/mysql.log
[root@centos7-01 mysql]# ll /data/mysql/mysql.log
-rw-rw-rw- 1 root root 0 May 10 20:57 /data/mysql/mysql.log
[root@centos7-01 mysql]# chmod guo+rw /data/mysql/mysql.log
[root@centos7-01 mysql]# ll /data/mysql/mysql.log
-rw-rw-rw- 1 root root 0 May 10 20:57 /data/mysql/mysql.log
[root@centos7-01 mysql]# mkdir /var/run/mariadb
[root@centos7-01 mysql]# chown -R mysql:mysql /var/run/mariadb/
[root@centos7-01 mysql]# touch /var/run/mariadb/mariadb.pid
[root@centos7-01 mysql]# ll /var/run/mariadb/
total 0
-rw-r--r-- 1 root root 0 May 10 20:59 mariadb.pid
[root@centos7-01 mysql]# chmod guo+rw /var/run/mariadb/mariadb.pid
[root@centos7-01 mysql]# ll /var/run/mariadb/
total 0
-rw-rw-rw- 1 root root 0 May 10 20:59 mariadb.pid
#9、初始化数据库文件并生成 root 空密码
[root@centos7-01 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
#10、启动MariaDB守护程序
[root@centos7-01 mysql]# ./bin/mysqld_safe --user=mysql --datadir=/data/mysql &
#11、测试MariaDB守护程序
[root@centos7-01 mysql-test]# cd ./mysql-test ; perl mysql-test-run.pl
#12、准备服务脚本和启动
[root@centos7-01 mysql-test]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7-01 mysql-test]# chkconfig --add mysqld
[root@centos7-01 mysql-test]# chkconfig --list mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@centos7-01 mysql-test]# systemctl start mysqld.service
#13、数据库的登录、查询，修改密码，Ctrl+D退出数据库
[root@centos7-01 mysql]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
#登录数据库 密码为空
[root@centos7-01 mysql]# ./bin/mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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 |
| test |
+--------------------+
4 rows in set (0.001 sec)
#修改密码为magedu
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY "magedu";
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
#从新登录数据库
[root@centos7-01 mysql]# ./bin/mysql -uroot -pmagedu
数据库版本 MySQL-5.7.37
#上传数据库文件，导入数据库
[root@centos7-02 local]# cd /usr/local/
[root@centos7-02 local]# ls
bin etc games hellodb_MyISAM.sql include lib lib64 libexec mysql mysql-5.7.37-el7-x86_64 mysql-5.7.37-el7-x86_64.tar.gz sbin share src
[root@centos7-02 local]# mysql -uroot -pmagedu < /usr/local/hellodb_MyISAM.sql
[root@centos7-02 local]# mysql -uroot -pmagedu
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hellodb;
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
数据库版本 MySQL-5.7.37
#1、服务端登录数据库
[root@centos7-01 ~]# mysql -uroot -pmagedu
#2、授权magedu用户，如单独授权某个数据库可改为hellodb.*。
mysql> GRANT ALL PRIVILEGES ON *.* TO magedu@'192.168.1.%' IDENTIFIED BY '123456';
#3、刷新权限
mysql> FLUSH PRIVILEGES;
#4、查询用户
select host,user from mysql.user;
+-------------+---------------+
| host | user |
+-------------+---------------+
| 192.168.1.% | magedu |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-------------+---------------+
4 rows in set (0.00 sec)
#5、删除用户
drop user 'magedu'@'192.168.1.%';
#6、查询用户权限
mysql> SHOW GRANTS FOR magedu@'192.168.1.%';
+-------------------------------------------------------+
| Grants for magedu@192.168.1.% |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'magedu'@'192.168.1.%' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
#7、客户端登录测试,客户端IP为192.168.1.101，服务端IP为10.0.0.131
[root@centos7-02 ~]# mysql -umagedu -h10.0.0.131 -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 10
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.一、主从复制
系统版本 CentOS 7.9
数据库版本 mysql5.7.38
主服务器 10.0.0.133（主机名：centos7-03）
从服务器 10.0.0.134（主机名：centos7-04）
#1、主服务器与从服务器yum源配置
[root@centos7-03 yum.repos.d]# vim mysql.repo
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
#2、主服务器与从服务器安装mysql
[root@centos7-03 ~]# yum -y install mysql-community-server
#3、服务器与从服务器启动mysql
[root@centos7-03 ~]# systemctl enable --now mysqld
#4、查看3306端口是否启动
[root@centos7-03 yum.repos.d]# ss -ntl
#5、查看默认密码
#主服务器密码
[root@centos7-03 yum.repos.d]# grep password /var/log/mysqld.log
2022-05-12T04:54:29.670817Z 1 [Note] A temporary password is generated for root@localhost: &RupPoFty7<g
#从服务器密码
[root@centos7-04 yum.repos.d]# grep password /var/log/mysqld.log
2022-05-12T12:04:33.601346Z 1 [Note] A temporary password is generated for root@localhost: *wI<H,J*h7fO
#6、登录数据库修改密码
[root@centos7-03 yum.repos.d]# mysql -uroot -p'&RupPoFty7<g'
[root@centos7-04 yum.repos.d]# mysql -uroot -p'*wI<H,J*h7fO'
mysql> alter user root@'localhost' identified by 'Aa@123456';
#7、主服务器配置
#修改my.cnf
[root@centos7-03 ~]# vim /etc/my.cnf
[mysqld]
log_bin #启用二级制日志
server-id=3 #当前节点设置一个全局惟一的ID号
#8、重启mysql
[root@centos7-03 ~]# service mysqld restart
#9、查看二进制日志的文件和位置
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> SHOW MASTER STATUS;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| centos7-03-bin.000002 | 154 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
#10、 登录数据库创建有复制权限的账号
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'Aa@123456';
#刷新权限
mysql> FLUSH PRIVILEGES;
#11、从服务器配置
#修改my.cnf
[root@centos7-04 ~]# vim /etc/my.cnf
server_id=4 #为当前节点设置一个全局惟的ID号
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
#12、重启mysql
[root@centos7-04 ~]# service mysqld restart
#13、从节点使用有复制权限的用户账号连接至主服务器，并启动复制线程，centos7-03-bin.000002 154这部分是主服务器查出来的
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
CHANGE MASTER TO
MASTER_HOST='10.0.0.133',
MASTER_USER='repluser',
MASTER_PASSWORD='Aa@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos7-03-bin.000002',
MASTER_LOG_POS=154;
#提示执行成功 Query OK, 0 rows affected, 2 warnings (0.01 sec)
#14、开启复制，查看状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.133
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos7-03-bin.000003
Read_Master_Log_Pos: 1845
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 2068
Relay_Master_Log_File: centos7-03-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1845
Relay_Log_Space: 4258
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 9b06a4f4-d1af-11ec-8a21-000c299d6fb7
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
#15、在主服务器增加数据
mysql> create database test2;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
mysql> use test2
mysql> create table t1(id int unsigned auto_increment primary key)
-> auto_increment = 1111;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1 |
+-----------------+
#16、查看是否自动同步到从服务器。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
mysql> use test2
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1 |
+-----------------+
系统版本 CentOS 7.9
数据库版本 MySQL-5.7.38
主服务器 10.0.0.133（主机名：centos7-03）
从服务器 10.0.0.134（主机名：centos7-04）
(1) 各节点使用一个惟一server_id
(2) 创建拥有复制权限的用户账号
(3) 定义自动增长id字段的数值范围各为奇偶
(4) 均把对方指定为主节点，并启动复制线程
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2#1、yum源配置
[root@centos7-03 yum.repos.d]# vim mysql.repo
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
#2、安装mysql
[root@centos7-03 ~]# yum -y install mysql-community-server
#3、启动mysql
[root@centos7-03 ~]# systemctl enable --now mysqld
#4、查看3306端口是否启动
[root@centos7-03 yum.repos.d]# ss -ntl
#5、查看随机密码
#主服务器1密码
[root@centos7-03 yum.repos.d]# grep password /var/log/mysqld.log
2022-05-12T04:54:29.670817Z 1 [Note] A temporary password is generated for root@localhost: &RupPoFty7<g
#主服务器2密码
[root@centos7-04 yum.repos.d]# grep password /var/log/mysqld.log
2022-05-12T12:04:33.601346Z 1 [Note] A temporary password is generated for root@localhost: *wI<H,J*h7fO
#6、登录主服务器1和主服务器2数据库并修改密码
[root@centos7-03 yum.repos.d]# mysql -uroot -p'&RupPoFty7<g'
[root@centos7-04 yum.repos.d]# mysql -uroot -p'*wI<H,J*h7fO'
#登录主服务器1和主服务器2，修改密码
mysql> alter user root@'localhost' identified by 'Aa@123456';
#7、主服务器1配置
#修改my.cnf
[root@centos7-03 ~]# vim /etc/my.cnf
[mysqld]
log_bin #启用二级制日志
server-id=3 #当前节点设置一个全局惟一的ID号
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
#8、重启mysql
[root@centos7-03 ~]# service mysqld restart
#9、查看二进制日志的文件和位置
#主服务器1
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> SHOW MASTER STATUS;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| centos7-03-bin.000002 | 154 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
#主服务器2
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
mysql> SHOW MASTER STATUS;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| centos7-04-bin.000003 | 154 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
#10、登录主服务器1和主服务器2数据库创建有复制权限的账号
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'Aa@123456';
#刷新权限
mysql> FLUSH PRIVILEGES;
#11、主服务器1使用有复制权限的用户账号连接至主服务器2，并启动复制线程，centos7-04-bin.000003 154这部分是主服务器1查出来的
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
CHANGE MASTER TO
MASTER_HOST='10.0.0.134',
MASTER_USER='repluser',
MASTER_PASSWORD='Aa@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos7-04-bin.000003',
MASTER_LOG_POS=154;
#提示执行成功 Query OK, 0 rows affected, 2 warnings (0.01 sec)
#12、开启复制，查看状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#13、开启复制，查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.134
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos7-04-bin.000004
Read_Master_Log_Pos: 610
Relay_Log_File: centos7-03-relay-bin.000003
Relay_Log_Pos: 833
Relay_Master_Log_File: centos7-04-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 610
Relay_Log_Space: 1216
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 4
Master_UUID: af6aac60-d1eb-11ec-9c3f-000c29588d7a
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
#14、主服务器2配置
#修改my.cnf
[root@centos7-04 ~]# vim /etc/my.cnf
server_id=4 #为当前节点设置一个全局惟的ID号
log-bin #启用二级制日志
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
#15、重启mysql
[root@centos7-04 ~]# service mysqld restart
#16、主服务器2使用有复制权限的用户账号连接至主服务器1，并启动复制线程，centos7-03-bin.000002 154这部分是主服务器1查出来的
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
CHANGE MASTER TO
MASTER_HOST='10.0.0.133',
MASTER_USER='repluser',
MASTER_PASSWORD='Aa@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos7-03-bin.000002',
MASTER_LOG_POS=154;
#提示执行成功 Query OK, 0 rows affected, 2 warnings (0.01 sec)
#17、开启复制，查看状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.133
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos7-03-bin.000003
Read_Master_Log_Pos: 1845
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 2068
Relay_Master_Log_File: centos7-03-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1845
Relay_Log_Space: 4258
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 9b06a4f4-d1af-11ec-8a21-000c299d6fb7
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
#18、在主服务器1增加数据
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> create database test2;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
mysql> use test2
mysql> create table t1(id int unsigned auto_increment primary key)
-> auto_increment = 1111;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1 |
+-----------------+
#19、查看是否自动同步到主服务器2
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
mysql> use test2
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1 |
+-----------------+
#20、在主服务器2增加库和表
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
mysql> create database test3;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
| test3 |
+--------------------+
mysql> use test3
mysql> create table t1(id int unsigned auto_increment primary key)
-> auto_increment = 222;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1 |
+-----------------+
#21、查看是否自动同步到主服务器1
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
| test3 |
+--------------------+
mysql> use test3
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| t1 |
数据库版本 MySQL-5.7.38
服务器1 10.0.0.133（主机名：centos7-03）
服务器2 10.0.0.134（主机名：centos7-04）
关闭防火墙 systemctl disable --now firewalld
一、全量备份以及还原
#服务器1、服务器2需要安装XtraBackup、libev
#1、下载XtraBackup
#https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
#2、下载libev-4.15-1.el6.rf.x86_64.rpm
http://rpmfind.net/linux/centos/7.9.2009/extras/x86_64/Packages/libev-4.15-7.el7.x86_64.rpm
#3、上传XtraBackup、libev
[root@centos7-03 ~]# ls
anaconda-ks.cfg libev-4.15-7.el7.x86_64.rpm percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
#4、安装libev
[root@centos7-03 ~]# yum -y install libev-4.15-7.el7.x86_64.rpm
#5、安装XtraBackup
[root@centos7-03 ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
#6、在服务器1做全量备份到/backup
#创建文件夹
[root@centos7-03 ~]# mkdir /backup
#7、完全备份
[root@centos7-03 backup]# xtrabackup -uroot -pAa@123456 --backup --target-dir=/backup/base
#8、将服务器1的备份传至服务器2，服务器2无需创建backup文件夹
[root@centos7-03 ~]# scp -r /backup root@10.0.0.134:/
#在服务器2准备还原
#9、预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos7-04 ~]# xtrabackup --prepare --target-dir=/backup/base
#10、复制到数据库目录
#注意：数据库目录必须为空，MySQL服务不能启动
[root@centos7-04 ~]# xtrabackup --copy-back --target-dir=/backup/base
#11、还原属性
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql
#12、启动服务
[root@localhost ~]# systemctl start mysqld
#13、查看是否还原成功
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
| test3 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test3
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)二、增量备份以及还原
#1、新建数据
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> create database test55;
mysql> create database test666;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
| test3 |
| test55 |
| test666 |
+--------------------+
#2、第一次增量备份backup/inc1
[root@centos7-03 base]# xtrabackup -uroot -pAa@123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#3、新建数据
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> create database test777;
Query OK, 1 row affected (0.00 sec)
mysql> create database test888;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
| test3 |
| test55 |
| test666 |
| test777 |
| test888 |
+--------------------+
#4、第二次增量备份
[root@centos7-03 base]# xtrabackup -uroot -pAa@123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#5、将服务器1的备份传至服务器2
[root@centos7-03 backup]# scp -r /backup/* 10.0.0.134:/backup/
#注意：数据库目录必须为空，MySQL服务不能启动
#删除服务器2数据库文件
[root@centos7-04 ~]# rm -rf /var/lib/mysql/*
#6、在服务器2准备还原，预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@centos7-04 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#7、合并第1次增量备份到完全备份
[root@centos7-04 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
#8、合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos7-04 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
#9、注意：数据库目录必须为空，MySQL服务不能启动
[root@centos7-04 ~]# xtrabackup --copy-back --target-dir=/backup/base
#10、注意文件夹权限
[root@centos7-04 ~]# chown -R mysql.mysql /var/lib/mysql/
#11、启动服务
[root@centos7-04 ~]# systemctl start mysqld
#12、查看是否还原成功
[root@centos7-04 ~]# mysql -uroot -p'Aa@123456'
mysql> create database test3;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
| test3 |
| test55 |
| test666 |
| test777 |
| test888 |
+--------------------+三、binlog备份与恢复
#1、先完全备份,用来模拟每天执行自动备份的数据，然后新增数据和删除数据，最后恢复被删除的数据
mysqldump -uroot -pAa@123456 -A -F --single-transaction --master-data=2 > /backup/all.sql
#2、备份后继续新增数据
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> show databases;
mysql> use test888;
create table t1(id int unsigned auto_increment primary key)auto_increment = 1;
create table t2(id int unsigned auto_increment primary key)auto_increment = 1;
mysql> show tables;
+-------------------+
| Tables_in_test888 |
+-------------------+
| t1 |
| t2 |
+-------------------+
#3、模拟误删除一个表
mysql> drop table t1;
mysql> show tables;
+-------------------+
| Tables_in_test888 |
+-------------------+
| t2 |
+-------------------+
#4、发现表被误删准备恢复时先停止数据库访问
[root@centos7-04 ~]# systemctl stop mysqld
#5、从完全备份中，找到二进制位置
[root@centos7-03 ~]# grep '\-\- CHANGE MASTER TO' /backup/all.sql
#查找到的二进制位置-- CHANGE MASTER TO MASTER_LOG_FILE='centos7-03-bin.000008', MASTER_LOG_POS=154;
#6、通过上述查到的二进制位置，备份从完全备份后的二进制日志
mysqlbinlog --start-position=154 /var/lib/mysql/centos7-03-bin.000008 > /backup/inc.sql
#7、找到误删除的语句，从备份中删除此语句
[root@centos8 ~]#vim /backup/inc.sql
#vim打开后可用搜索找到删除操作的记录/DROP TABLE,发现此条数据将其删除并保存DROP TABLE `t1` /* generated by server */
#如果文件过大，建议使用sed实现删除DROP TABL数据
[root@centos7-03 ~]# sed -i.bak '/^DROP TABLE/d' /backup/inc.sql
#8、利用完全备份和修改过的二进制日志进行还原
#开启数据库并登录，关闭二进制记录sql_log_bin，恢复数据库all.sql和修改后的inc.sql数据，最后开启二进制记录sql_log_bin
[root@centos7-03 ~]# systemctl start mysqld
[root@centos7-03 ~]# mysql -uroot -p'Aa@123456'
mysql> set sql_log_bin=0;
mysql> source /backup/all.sql;
mysql> source /backup/inc.sql
mysql> set sql_log_bin=1;
#9、查看是否还原成功
mysql> show databases;
mysql> use test888;
mysql> show tables;
+-------------------+
| Tables_in_test888 |
+-------------------+
| t1 |
| t2 |
+-------------------+系统版本 CentOS 7.9
数据库版本 MariaDB 10.5
MyCAT服务器 10.0.0.133（主机名：centos7-03）
主服务器 10.0.0.134（主机名：centos7-04）
从服务器 10.0.0.135（主机名：centos7-05）
客户端 10.0.0.136（主机名：centos7-06）
关闭防火墙： systemctl disable --now firewalld
#1、yum源配置
vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = https://mirrors.nju.edu.cn/mariadb/yum/10.5/centos7-amd64
gpgkey=https://mirrors.nju.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
#2、主从服务器安装mysql数据库
yum -y install mariadb-server
#3、修改写服务器和读服务器上的my.cnf配置文件
#主服务器my.cnf
[mysqld]
server-id = 1
log-bin
#从服务器my.cnf
[mysqld]
server-id = 2
#4、重启数据库
systemctl restart mysqld
#5、查看主服务器二进制日志的文件和位置
[root@centos7-04 ~]# mysql -uroot -p
MariaDB [(none)]> SHOW MASTER STATUS;
+-----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| centos7-04-bin.000001 | 333 | | |
+-----------------------+----------+--------------+------------------+
#6、主服务器创建有复制权限的账号
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.%' IDENTIFIED BY 'replpass';
#刷新权限
mysql> FLUSH PRIVILEGES;
#7、从服务器使用有复制权限的用户账号连接至写服务器，并启动复制线程，centos7-04-bin.000001 333这部分是主写服务器查出来的
[root@centos7-05 ~]# mysql -uroot -p
CHANGE MASTER TO
MASTER_HOST='10.0.0.134',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos7-04-bin.000001',
MASTER_LOG_POS=333;
#提示执行成功 Query OK, 0 rows affected, 2 warnings (0.01 sec)
#8、开启复制，查看状态
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#9、开启复制，查看状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.134
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos7-04-bin.000001
Read_Master_Log_Pos: 655
Relay_Log_File: centos7-05-relay-bin.000002
Relay_Log_Pos: 882
Relay_Master_Log_File: centos7-04-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 655
Relay_Log_Space: 1196
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 1
Slave_Transactional_Groups: 0
#10、mycat服务器安装java，mycat需要java环境
#需要安装java
[root@centos7-03 ~]# yum -y install java
#确认安装成功
[root@centos7-03 ~]# java -version
openjdk version "1.8.0_262"
OpenJDK Runtime Environment (build 1.8.0_262-b10)
OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)
#11、下载mycat
#http://dl.mycat.org.cn/1.6.7.6/20220419132943/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
#12、上传mycat
[root@centos7-03 ~]# ls
anaconda-ks.cfg Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
#13、安装mycat
#创建文件夹
[root@centos7-03 ~]# mkdir /apps
#解压缩
[root@centos7-03 ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
#14、配置环境变量
[root@centos7-03 apps]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos7-03 apps]# source /etc/profile.d/mycat.sh
#15、启动mycat，注意: 此步可能启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@centos7-03 apps]# mycat start
#16、#可以看到打开多个端口,其中8066端口用于连接MyCAT
[root@centos7-03 ~]# ss -ntlp
#17、修改server.xml文件配置Mycat的连接信息
[root@centos7-03 ~]# vim /apps/mycat/conf/server.xml
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面
#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连
接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property
name="processors">32</property> #--> 删除#后面此部分
<user name="root"> #连接Mycat的用户名
<property name="password">123456</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相
对应
</user>
</mycat:server>
#修改后的内容
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
#18、修改schema.xml实现读写分离策略
[root@centos7-03 ~]# vim /apps/mycat/conf/schema.xml
<!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="***mycat***" /> #其中mycat表
示后端服务器实际的数据库名称
<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.134:3306" user="root"
password="123456">***
***<readHost host="host2" url="10.0.0.135:3306" user="root" password="123456"
/>***
</writeHost>
</dataHost>
</mycat:schema>
#以上***部分表示原配置文件中需要修改的内容
#修改后的内容
<?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.134:3306" user="root"
password="123456">
<readHost host="host2" url="10.0.0.135:3306" user="root"
password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
#注意大小写
#重启mycat
[root@centos7-03 ~]# mycat restart
#19、#可以看到3306端口
[root@centos7-03 ~]# ss -ntlp
#注意我这里配置的/apps/mycat/conf/schema.xml数据库为hellodb，根据自己的实际情况填写
#在主服务器导入hellodb数据用做测试
[root@centos7-04 ~]# mysql < hellodb_MyISAM.sql
#20、在主服务器增加用户权限
[root@centos7-04 ~]# mysql -uroot -p
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'10.0.0.%' IDENTIFIED BY '123456';
MariaDB [(none)]> flush privileges;
#21、在客户端上连接mycat
[root@centos7-06 ~]# mysql -uroot -p123456 -h 10.0.0.133
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#22、测试读写分离测试，为了看清楚测试结果，先关闭了从服务器的数据同步stop slave;，否则修改数据时从服务器会自动同步过来，影响测试效果。
#在从服务器上查看工作日志，先开启general
MariaDB [(none)]> show variables like 'general%';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| general_log | OFF |
| general_log_file | centos7-05.log |
+------------------+----------------+
2 rows in set (0.001 sec)
MariaDB [(none)]> set global general_log=1;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show variables like 'general%';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| general_log | ON |
| general_log_file | centos7-05.log |
+------------------+----------------+
#在主服务器上查看工作日志，先开启general
MariaDB [(none)]> show variables like 'general%';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| general_log | OFF |
| general_log_file | centos7-04.log |
+------------------+----------------+
2 rows in set (0.002 sec)
MariaDB [(none)]> set global general_log=1;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show variables like 'general%';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| general_log | ON |
| general_log_file | centos7-04.log |
+------------------+----------------+
#客户端登录mycat，执行新增数据
[root@centos7-01 ~]# mysql -uroot -p123456 -h 10.0.0.133
mysql> insert teachers values(10,'xiaoqiang',30,'M');
Query OK, 1 row affected (0.00 sec)
#主服务器日志记录到执行结果
[root@centos7-04 ~]# tail -f /var/lib/mysql/centos7-04.log
220514 17:22:35 53 Query SET names utf8;insert teachers values(10,'xiaoqiang',30,'M')
220514 17:22:41 36 Query select user()
220514 17:22:51 52 Query select user()
#从服务器日志记录只有心跳数据。
[root@centos7-05 ~]# tail -f /var/lib/mysql/centos7-05.log
220514 17:22:31 12 Query select user()
220514 17:22:41 9 Query select user()
220514 17:22:51 8 Query select user()
#客户端登录mycat，执行查询
mysql> select * from students;
#主服务器日志记录只有心跳数据
[root@centos7-04 ~]# tail -f /var/lib/mysql/centos7-04.log
220514 17:25:01 36 Query select user()
220514 17:25:11 52 Query select user()
220514 17:25:21 51 Query select user()
#从服务器日志记录到执行结果
[root@centos7-05 ~]# tail -f /var/lib/mysql/centos7-05.log
220514 17:25:04 12 Query select * from students
220514 17:25:11 8 Query select user()
220514 17:25:21 9 Query select user()
#通过查看日志读写分离已经完成，从服务器恢复同步。
MariaDB [(none)]> start slave;
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？