MySQL经验总结

RPM安装

安装官网YUM源
sudo yum install mysql80-community-release-el7-11.noarch

官网安装
sudo yum install mysql80-community-release-el7-11.noarch.rpm

查看默认密码
sudo grep password /var/log/mysqld.log 

修改密码
sudo mysqladmin -uroot -p'xxxxxx' password 'XXxxx'

登录查看
mysql -uroot -p'XXxxx'
show databases;

编译安装

安装编译软件
yum install -y gcc gcc-c++ cmake ncurses ncurses-devel bison

编译
cd mysql-8.0
cmake \

安装
cd mysql-8.0
make -j 2 && make install

创建用户
useradd mysql -s /sbin/nologin
chown -R mysql.mysql /usr/local/mysql

创建配置文件
vi /etc/my.cnf

添加环境变量
echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile

初始化
mysql --initialize-insecure --user=mysql --baseurl=/usr/local/mysql --datadir=/usr/loacl/mysql/data

创建systemd进程
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system
systemctl daemon-reload
systemctl enable mysqld --now
 

自建YUM

安装FTP
yum install -y vsftpd
mkdir /var/ftp/mysql8/

安装官网YUM源
yum install -y mysql80-community-release-el7-11.noarch

下载安装包
yum install -y --downloadonly mysql-community-server
cp -rf /var/cache/yum/x86_64/7/mysql80-community/packages/* /var/ftp/mysql8/

创建依赖关系
cd /var/ftp/mysql8/
yum provides createrepo
yum install -y createrepo_c
createrepo /var/ftp/mysql8/ 

启动FTP
vi /etc/vsftpd/vsftpd.conf
 anonymous_enable=YES
systemctl start vsftpd



客户端配置
vi /etc/yum.repo.d/mysql.repo
[mysql8]
name=ftp
baseurl=ftp://x.x.x.x/mysql8
gpgcheck=0
enabled=1

密码破解

[root@node1 ~]# echo 'skip-grant-tables' >>/etc/my.cnf
[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# mysql -uroot
mysql> update mysql.user set authentication_string='' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update mysql.user set authentication_string='' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> \q
Bye


[root@node1 ~]# sed -i 's/^skip.*//g' /etc/my.cnf
[root@node1 ~]# mysqladmin -uroot -p  password 'JKzone@123'
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

日志管理

Error log、查询日志、二进制日志、中继日志、slow log、DDL log


一、Error log
log-error=


二、二进制日志
datadir=

xxx-bin.00xxx      <<---该二进制文件，刷新后自动增加
xxx-bin.index      <<---所有二进制文件

[root@node1 ~]# echo -e "log_bin \n server-id=1" >> /etc/my.cnf       <<---开启
[root@node1 ~]# systemctl restart mysqld
[root@node1 mysql]# mysqlbinlog -v /var/lib/mysql/node1-bin.000002   <<---查看



三、slow log
/var/lib/mysql/node1-slow.log 

[root@node1 mysql]# echo -e 'slow_query_log=1 \n long_query_time=3' >>/etc/my.cnf  <<---开启
[root@node1 mysql]# systemctl restart mysqld



四、查询日志
/var/lib/mysql/node1.log 

[root@node1 mysql]# echo 'general_log=1' >> /etc/my.cnf      <<---开启
[root@node1 mysql]# systemctl restart mysqld

备份还原

percona

Percona
安装
[root@node1 ~]# yum install https://repo.percona.com/yum/percona-release-latest. noarch.rpm
[root@node1 ~]# yum install percona-xtrabackup-80
[root@node1 ~]# mkdir -p /data/backup


--------------------------------完整备份--------------------------------------------
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -pTom@123456 -H localhost -P 3306     <<---备份
.
.
.
2023-12-04T11:14:28.758182+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (21250950) to (21250950) was copied.
2023-12-04T11:14:28.967631+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK


prepare阶段
[root@node1 base]# xtrabackup --prepare --target-dir=/data/backup/base/    
.
.
2023-12-04T11:41:29.356366+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 21251094
2023-12-04T11:41:29.357961+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[root@node1 base]# xtrabackup --copy-back --target-dir=/data/backup/base/        <<---还原
[root@node1 base]# chown -R mysql.mysql /var/lib/mysql
[root@node1 base]# systemctl restart mysqld




---------------------------------------增量备份-------------------------------------
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -pTom@123456 -H localhost -P 3306      <<---第一天完整备份
[root@node1 base]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/in1/ --incremental-basedir=/data/backup/base/ -uroot -pTOm@123456 -H localhost -P 3306                    <<---第二天增量备份
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/in2/ --incremental-basedir=/data/backup/in1/ -uroot -pTOm@123456 -H localhost -P 3306                        <<---第三天增量备份


prepare阶段
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/in1
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/in2       ###最后一天不加"--apply-log-only"


[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base                        <<---还原
[root@node1 ~]# chown -R mysql.mysql /var/lib/mysql
[root@node1 ~]# systemctl restart mysqld




-------------------------------------差异备份---------------------------------------
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -pTom@123456 -H localhost -P 3306      <<---第一天完整备份
[root@node1 base]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/com1/ --incremental-basedir=/data/backup/base/ -uroot -pTOm@123456 -H localhost -P 3306                    <<---第二天差异备份
[root@node1 base]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/com2/ --incremental-basedir=/data/backup/base/ -uroot -pTOm@123456 -H localhost -P 3306                    <<---第三天增量备份


prepare阶段
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/                        <<---第一天
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/com2       ###最后一天，没有"--apply-log-only"


[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base                        <<---还原
[root@node1 ~]# chown -R mysql.mysql /var/lib/mysql
[root@node1 ~]# systemctl restart mysqld




---------------------------------压缩备份-------------------------------------------
[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --compress --target-dir=/data/backup/compress/ -uroot -pmarry@123 -H localhost -P 3306    <<---完整压缩备份，增量和差异参考上面

[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --decompress --target-dir=/data/backup/compress            <<---完整解压缩，增量和差异参考上面


prepare阶段
[root@node1 ~]# xtrabackup --prepare --target-dir=/data/backup/compress



[root@node1 ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/compress             <<---还原
[root@node1 ~]# chown -R mysql.mysql /var/lib/mysql
[root@node1 ~]# systemctl restart mysqld

dump

[root@node1 ~]# mysqldump -uroot -p'JKzone@123' --all-databases --single-transaction --master-data=2 --flush-logs > /data/backup/`date +%F-%H`.log      <<---备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.

--all-databases                <<---全部数据库
--single-transaction           <<---热备份
--master-data=2                <<---记录二进制位置
--databases xxx1 xxx2          <<---多个数据库
--flush-logs                   <<---日志轮转


[root@node1 ~]# systemctl start mysqld
[root@node1 ~]# grep 'password' /var/log/mysqld.log         <<---查看新密码
2023-11-27T02:51:14.379096Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Phb1lrr(o/JW
2023-12-05T02:55:58.941641Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: #Yds+;0o3:a*
[root@node1 ~]# mysqladmin -uroot -p'#Yds+;0o3:a*' password 'Tom@123456'         
      <<---修改密码
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@node1 ~]# mysql -uroot -p'Tom@123456' < /data/backup/2023-12-05-10.log     
      <<---还原
mysql: [Warning] Using a password on the command line interface can be insecure.



--------------------------------二进制日志------------------------------------------
[root@node1 ~]# cp /var/lib/mysql/*bin* /data/backup/        <<---备份二进制日志
[root@node1 ~]# grep 'node1-bin' /data/backup/2023-12-05-10.log         <<---查看备份的二进制点
[root@node1 ~]# mysqlbinlog /data/backup/node1-bin.000017 /data/backup/node1-bin.000018 --start-position=157 |mysql -uroot -p'Tom@123456'            <<---恢复备份点后的数据
mysql: [Warning] Using a password on the command line interface can be insecure.

binlog

二进制日志记录数据库实时的操作，通过还原二进制日志，能恢复备份时间点后的数据


[root@node1 ~]# cp /var/lib/mysql/*bin* /data/backup/        <<---备份二进制日志
[root@node1 ~]# grep 'node1-bin' /data/backup/2023-12-05-10.log         <<---查看备份的二进制点
[root@node1 ~]# mysqlbinlog /data/backup/node1-bin.000017 /data/backup/node1-bin.000018 --start-position=157 |mysql -uroot -p'Tom@123456'            <<---还原二进制日志
mysql: [Warning] Using a password on the command line interface can be insecure.

库、表备份

[root@node1 ~]# echo 'secure-file-priv=/backup' >> /etc/my.cnf         <<---数据库授权
[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# chown -R mysql.mysql /backup/                          <<---系统文件授权
[root@node1 ~]# mysql -uroot -p
Enter password: 
mysql> select * from school.t1 into outfile '/backup/school.t1';       <<---方法一：备份
Query OK, 4 rows affected (0.00 sec)


[root@node1 ~]# mysql -uroot -pMarry@123.com -e 'select * from school.t1' > /backup/school.t2            <<---方法二：备份
mysql: [Warning] Using a password on the command line interface can be insecure.




----------------------------------还原-----------------------------------------------
mysql> load data infile '/backup/school.t1' into table school.t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0