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