mysql进阶

二进制格式mysql安装

[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug  kernels  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# groupadd -r mysql
groupadd：“mysql”组已存在
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
useradd：用户“mysql”已存在
[root@localhost src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@localhost src]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz  mysql 
'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz'

[root@localhost local]# chown -R mysql:mysql /usr/local/mysql
[root@localhost local]# ll -d /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 12月 30 05:01 /usr/local/mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/







[root@localhost src]# ls /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64/
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost src]#  echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost src]# . /etc/profile.d/mysql.sh
[root@localhost src]# echo $PATH
/usr/local/mysql/bin:/usr/local/httpd/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost src]# 






[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown mysql:mysql /opt/data/
[root@localhost ~]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 12月 29 00:56 data
[root@localhost ~]# 




启动数据库




[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service




初始化数据库




[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2020-12-30T10:06:24.338513Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-30T10:06:24.759563Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-30T10:06:24.792643Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-30T10:06:24.852711Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: acb4ac01-4a86-11eb-b585-000c2975fe91.
2020-12-30T10:06:24.853487Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-30T10:06:25.543391Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-30T10:06:25.635377Z 1 [Note] A temporary password is generated for root@localhost: )9Ms#lXod+s=
[root@localhost local]# 







 mysql常用配置文件参数：


 





参数说明








port = 3306
设置监听端口







socket = /tmp/mysql.sock
指定套接字文件位置







basedir = /usr/local/mysql
指定MySQL的安装路径







datadir = /data/mysql
指定MySQL的数据存放路径







pid-file = /data/mysql/mysql.pid
指定进程ID文件存放路径







user = mysql
指定MySQL以什么用户的身份提供服务







skip-name-resolve
禁止MySQL对外部连接进行DNS解析
使用这一选项可以消除MySQL进行DNS解析的时间。
若开启该选项，则所有远程主机连接授权都要使用IP地址方
式否则MySQL将无法正常处理连接请求		

















生成配置：




[root@localhost local]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve




启动：




[root@localhost local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data




启动mysql服务：




[root@localhost local]# mysql -uroot -p'y!=Qgsthp3hu'
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.31

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 




重设密码：




mysql> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye
[root@localhost local]# chkconfig --add mysqld
[root@localhost local]# chkconfig mysqld on
[root@localhost local]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld             0:关    1:关    2:开    3:开    4:开    5:开    6:关
[root@localhost local]# 






[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 




 


 





备份方案特点





全量备份
全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。
备份时间长		






增量备份
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份
与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量
备份后所产生的增加和修改的文件，如此类推。

没有重复的备份数据
备份时间短
恢复数据时必须按一定的顺序进行		






差异备份
备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。		











全量备份




[root@localhost ~]# mysql -uroot -p123
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.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school
Database changed
mysql> use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.12 sec)

mysql> insert student(name,age)values('tom',10),('zhangshan',20),('lisi',30);
Query OK, 3 rows affected (0.45 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> quit
Bye


[root@localhost ~]# mysqldump -uroot -p123 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.




[root@localhost ~]# ls
all-202012310225.sql
all.sql
anaconda-ks.cfg


[root@localhost ~]# file all.sql 


all.sql: UTF-8 Unicode text, with very long lines






[root@localhost ~]# mysql -uroot -pcsl123 -e 'drop database school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pcsl123 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pcsl123 -e 'select * from school.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   10 |
|  2 | zhangshan |   20 |
|  3 | lisi |   30 |
+----+----------+------+




在school查入数据




[root@localhost ~]# mysql
mysql> use school;
mysql> create table student1(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
mysql> insert student1(name,age) values('aa',13),('bb',14),('cc',22),('dd',33);
mysql> create table student2(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| student1         |
| student2         |
+------------------+
mysql> select * from student1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | aa     |   13 |
|  2 | bb      |   14 |
|  3 | cc |   22 |
|  4 | dd |   33 |
+----+----------+------+
mysql> quit
[root@localhost ~]# mysqldump -uroot school student1 > table_student1.sql
[root@localhost ~]# mysqldump -uroot school > table_school.sql
mysql> create database information;
mysql> use information;
mysql> create table teacher(id int not null primary key auto_increment,name varchar(100)not null,age tinyint(4));
mysql> insert teacher(name,age) values('nmi',31),('hil',43),('alice',28);
mysql> exit




删除库school，使用备份文件table_school.sql恢复库school数据




mysql> drop database school;
mysql> create database school;
mysql> use school;
mysql> source table_school.sql;




差异备份




[root@localhost ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
skip-grant-tables
log-bin=mysql_bin
server-id=1
[root@localhost ~]# service mysqld restart
[root@localhost ~]# rm -rf *.sql
[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202012291840.sql
mysql> use school;
mysql> insert student2(name,age) values('tom',21),('zhangshan',22),('lisi',23);
mysql> update student2 set age=21 where id=3;
mysql> drop database school;






[root@localhost ~]# ls /opt/data/
auto.cnf         client-key.pem  ib_logfile1                mysql_bin.000002  public_key.pem
ca-key.pem       ib_buffer_pool  ibtmp1                     mysql_bin.index   server-cert.pem
ca.pem           ibdata1         localhost.localdomain.err  mysql.pid         server-key.pem
client-cert.pem  ib_logfile0     mysql_bin.000001           private_key.pem
[root@localhost ~]# mysqladmin -uroot flush-logs
[root@localhost ~]# ls /opt/data/
auto.cnf         client-key.pem  ib_logfile1                mysql_bin.000002  private_key.pem
ca-key.pem       ib_buffer_pool  ibtmp1                     mysql_bin.000003  public_key.pem
ca.pem           ibdata1         localhost.localdomain.err  mysql_bin.index   server-cert.pem
client-cert.pem  ib_logfile0     mysql_bin.000001           mysql.pid         server-key.pem
[root@localhost ~]# mysql -uroot -p123 < all
all-202012310225.sql  all.sql               
[root@localhost ~]# mysql -uroot -p123 < all-202012310225.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  show binlog events in 'mysql_bin.000002'\G
*************************** 1. row ***************************
   Log_name: mysql_bin.000002
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.31-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql_bin.000002
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql_bin.000002
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql_bin.000002
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 319
       Info: create database school
*************************** 5. row ***************************
   Log_name: mysql_bin.000002
        Pos: 319
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 384
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
   Log_name: mysql_bin.000002
        Pos: 384
 Event_type: Query
  Server_id: 1
End_log_pos: 555
       Info: use `school`; create table student(id int not null primary key auto_increment,name varchar(50),age tinyint)
*************************** 7. row ***************************
   Log_name: mysql_bin.000002
        Pos: 555
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 620
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 8. row ***************************
   Log_name: mysql_bin.000002
        Pos: 620
 Event_type: Query
  Server_id: 1
End_log_pos: 694
       Info: BEGIN
*************************** 9. row ***************************
   Log_name: mysql_bin.000002
        Pos: 694
 Event_type: Table_map
  Server_id: 1
End_log_pos: 750
       Info: table_id: 90 (school.student)
*************************** 10. row ***************************
   Log_name: mysql_bin.000002
        Pos: 750
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 822
       Info: table_id: 90 flags: STMT_END_F
*************************** 11. row ***************************
   Log_name: mysql_bin.000002
        Pos: 822
 Event_type: Xid
  Server_id: 1
End_log_pos: 853
       Info: COMMIT /* xid=11 */
*************************** 12. row ***************************
   Log_name: mysql_bin.000002
        Pos: 853
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 918
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 13. row ***************************
   Log_name: mysql_bin.000002
        Pos: 918
 Event_type: Query
  Server_id: 1
End_log_pos: 1016
       Info: drop database school
*************************** 14. row ***************************
   Log_name: mysql_bin.000002
        Pos: 1016
 Event_type: Rotate
  Server_id: 1
End_log_pos: 1063
       Info: mysql_bin.000003;pos=4
14 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# mysqlbinlog --stop-position=918 /opt/data/mysql_bin.000002|mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.




安装这个包




[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# yum -y install percona*




使用innobackupex命令全量备份




[root@localhost ~]# innobackupex --user=root --password=123 --host=192.168.122.134 /backups/
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1 
xtrabackup: recognized client arguments: 
210103 02:04:05 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

210103 02:04:05  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.122.134' as 'root'  (using password: YES).
210103 02:04:05  version_check Connected to MySQL server
210103 02:04:05  version_check Executing a version check against the server...
DBD::mysql::db selectall_hashref failed: Table 'performance_schema.session_variables' doesn't exist at - line 1221.
[root@localhost backups]# ll
总用量 0
drwxr-x---. 2 root root 6 1月   3 02:04 2021-01-03_02-04-05
[root@localhost backups]# ll 2021-01-03_02-04-05/
total 13450
-rw-r-----. 1 root root      487 Dec 29 21:41 backup-my.cnf   
-rw-r-----. 1 root root      848 Dec 29 21:41 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 29 21:41 ibdata1
drwxr-x---. 2 root root       58 Dec 29 21:41 information
drwxr-x---. 2 root root     4096 Dec 29 21:41 mysql
drwxr-x---. 2 root root     8192 Dec 29 21:41 performance_schema
drwxr-x---. 2 root root      138 Dec 29 21:41 school
drwxr-x---. 2 root root     8192 Dec 29 21:41 sys




删除数据库再恢复




[root@localhost backups]# /etc/init.d/mysqld stop 
[root@localhost backups]# innobackupex --apply-log /backups/2021-01-03_02-04-05/ 
[root@localhost backups]# rm -rf /opt/data/
[root@localhost backups]# innobackupex --copy-back /backups/2020-12-29_21-41-12/
201229 22:19:15 completed OK! 
[root@localhost backups]# ll /opt/data/ 
total 132925
-rw-r-----. 1 root root      848 Dec 29 22:19 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 29 22:19 ibdata1
-rw-r-----. 1 root root 50331648 Dec 29 22:19 ib_logfile0
-rw-r-----. 1 root root 50331648 Dec 29 22:19 ib_logfile1
-rw-r-----. 1 root root 12582912 Dec 29 22:19 ibtmp1
drwxr-x---. 2 root root       58 Dec 29 22:19 information
drwxr-x---. 2 root root     4096 Dec 29 22:19 mysql
drwxr-x---. 2 root root     8192 Dec 29 22:19 performance_schema
drwxr-x---. 2 root root      138 Dec 29 22:19 school
drwxr-x---. 2 root root     8192 Dec 29 22:19 sys
-rw-r-----. 1 root root       24 Dec 29 22:19 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root      489 Dec 29 22:19 xtrabackup_info
-rw-r-----. 1 root root        1 Dec 29 22:19 xtrabackup_master_key_id
[root@localhost backups]# chown -R mysql.mysql /opt/data/
[root@localhost backups]# /etc/init.d/mysqld start 




 






            

            
posted @ 
2020-12-30 15:28 
cbcbage 
阅读(136) 
评论(1) 
 
收藏 
举报