MariaDB [wzw]> show tables; +---------------+ | Tables_in_wzw | +---------------+ | student | +---------------+ 1 row in set (0.000 sec) 对student表进行备份 [root@node1 opt]# mysqldump -uroot -p wzw student > student-$(date '+%Y%m%d').sql Enter password: [root@node1 opt]# ls playbook student-20210826.sql // 删除表 MariaDB [wzw]> drop table student; Query OK, 0 rows affected (0.008 sec) MariaDB [wzw]> show tables; Empty set (0.000 sec) // 对表进行恢复 [root@node1 opt]# mysql -uroot -p wzw < student-20210826.sql Enter password: [root@node1 opt]# MariaDB [wzw]> show tables; +---------------+ | Tables_in_wzw | +---------------+ | student | +---------------+ 1 row in set (0.000 sec)
MariaDB [wzw]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | wzw | +--------------------+ 5 rows in set (0.000 sec) [root@node1 opt]# mysqldump -uroot -p --all-databases > all-$(date '+%Y%m%d').sql Enter password: [root@node1 opt]# // 对库进行恢复 MariaDB [wzw]> drop database wzw ; Query OK, 1 row affected (0.005 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.000 sec) [root@node1 opt]# mysql -uroot -p < all-20210826.sql Enter password: [root@node1 opt]# MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | wzw | +--------------------+ 5 rows in set (0.001 sec)
[root@node1 mysql]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-2021-08-26.sql Enter password: [root@node1 opt]# [root@node1 mysql]# ls aria_log.00000001 ibtmp1 mysql_upgrade_info aria_log_control multi-master.info performance_schema ib_buffer_pool mysql test ibdata1 mysql-bin.000002 wzw ib_logfile0 mysql-bin.index ib_logfile1 mysql.sock
MariaDB [wzw]> update student set age=18 where id=10; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [wzw]> delete from student where name='jerry'; Query OK, 1 row affected (0.001 sec) MariaDB [wzw]> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangsan | 26 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 18 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 9 rows in set (0.000 sec)
// 删除数据库
MariaDB [wzw]> drop database wzw; Query OK, 1 row affected (0.003 sec)
// 刷新库的日志文件
[root@node1 ~]# mysqladmin -uroot -p flush-logs Enter password: [root@node1 ~]# [root@node1 mysql]# ls aria_log.00000001 ibtmp1 mysql.sock aria_log_control multi-master.info mysql_upgrade_info ib_buffer_pool mysql performance_schema ibdata1 mysql-bin.000002 test ib_logfile0 mysql-bin.000003 ib_logfile1 mysql-bin.index
[root@node1 opt]# mysql -uroot -p < all-2021-08-26.sql Enter password: [root@node1 opt]# MariaDB [wzw]> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangsan | 26 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 10 rows in set (0.000 sec)
// 先查看误删数据库所在的位置
MariaDB [wzw]> show binlog events in 'mysql-bin.000002'; +------------------+-----+-------------------+-----------+-------------+---------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------------+-----------+-------------+---------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.17-MariaDB-log, Binlog ver: 4 | | mysql-bin.000002 | 256 | Gtid_list | 1 | 285 | [] | | mysql-bin.000002 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000001 | | mysql-bin.000002 | 328 | Binlog_checkpoint | 1 | 371 | mysql-bin.000002 | | mysql-bin.000002 | 371 | Gtid | 1 | 413 | BEGIN GTID 0-1-1 | | mysql-bin.000002 | 413 | Query | 1 | 517 | use `wzw`; delete from student where name='jerry' | | mysql-bin.000002 | 517 | Xid | 1 | 548 | COMMIT /* xid=437 */ | | mysql-bin.000002 | 548 | Gtid | 1 | 590 | BEGIN GTID 0-1-2 | | mysql-bin.000002 | 590 | Query | 1 | 693 | use `wzw`; update student set age=18 where id=10 | | mysql-bin.000002 | 693 | Xid | 1 | 724 | COMMIT /* xid=439 */ | | mysql-bin.000002 | 724 | Gtid | 1 | 766 | GTID 0-1-3 | | mysql-bin.000002 | 766 | Query | 1 | 849 | drop database wzw | | mysql-bin.000002 | 849 | Rotate | 1 | 896 | mysql-bin.000003;pos=4 | +------------------+-----+-------------------+-----------+-------------+---------------------------------------------------+ 13 rows in set (0.001 sec)
这里的766是你开始删除库之前的修改过数据的位置。 mysqlbinlog --stop-position=766 /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p MariaDB [wzw]> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangsan | 26 | | 7 | lisi | 50 | | 8 | chenshuo | 10 | | 9 | wangwu | 100 | | 10 | qiuyi | 18 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 9 rows in set (0.000 sec)
MariaDB [test]> create table xx (a varchar(20),b varchar(20),c varchar(20)); Query OK, 0 rows affected (0.004 sec) MariaDB [test]> desc xx; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | varchar(20) | YES | | NULL | | | b | varchar(20) | YES | | NULL | | | c | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.001 sec) MariaDB [test]> select * from xx; +------+------+------+ | a | b | c | +------+------+------+ | 1 | a | ss | | 1 | a | ss | | 1 | a | ss | | 1 | a | ss | | 1 | a | ss | | 1 | b | qq | | 1 | b | qq | | 1 | b | qq | | 1 | b | qq | +------+------+------+ 9 rows in set (0.000 sec)
以b列来进行分组(count groupby 的应用)
统计b这一列中a和b出现的次数
MariaDB [test]> select count(a),b from xx group by b; +----------+------+ | count(a) | b | +----------+------+ | 5 | a | | 4 | b | +----------+------+ 2 rows in set (0.001 sec) 统计c这一列中qq和ss出现的次数 MariaDB [test]> select count(a),c from xx group by c; +----------+------+ | count(a) | c | +----------+------+ | 4 | qq | | 5 | ss | +----------+------+ 2 rows in set (0.001 sec) 统计xx这个表中c这一列qq和ss出现的次数 MariaDB [test]> select c,count(c) from xx group by c; //括号里的c可以是这个表里的其他数字或字母 +------+----------+ | c | count(c) | +------+----------+ | qq | 4 | | ss | 5 | +------+----------+ 2 rows in set (0.000 sec) 求age这一列的平均值 MariaDB [wzw]> select avg(age) from student; +----------+ | avg(age) | +----------+ | 33.0000 | +----------+ 1 row in set (0.000 sec) 求age这一列的和 MariaDB [wzw]> select sum(age) from student; +----------+ | sum(age) | +----------+ | 297 | +----------+ 1 row in set (0.000 sec)
意思是a表和b的表的内连接中a的id和b的parent_id相等的有哪些
MariaDB [test]> select a.*,b.* from a inner join b on a.id=b.parent_id; +------+----------+------+------+-----------+ | id | name | id | job | parent_id | +------+----------+------+------+-----------+ | 1 | zhangsan | 1 | 23 | 1 | | 2 | lisi | 2 | 34 | 2 | +------+----------+------+------+-----------+ 2 rows in set (0.001 sec)
意思是a表和b的表的左连接中以a表为标准进行比较,显示b表与a表的相同和不同的地方有哪些
MariaDB [test]> select a.*,b.* from a left join b on a.id=b.parent_id; +------+----------+------+------+-----------+ | id | name | id | job | parent_id | +------+----------+------+------+-----------+ | 1 | zhangsan | 1 | 23 | 1 | | 2 | lisi | 2 | 34 | 2 | | 3 | wangwu | NULL | NULL | NULL | +------+----------+------+------+-----------+
意思是a表和b的表的左连接中以b表为标准进行比较,显示b表与a表的相同和不同的地方有哪些
MariaDB [test]> select a.*,b.* from a right join b on a.id=b.parent_id; +------+----------+------+------+-----------+ | id | name | id | job | parent_id | +------+----------+------+------+-----------+ | 1 | zhangsan | 1 | 23 | 1 | | 2 | lisi | 2 | 34 | 2 | | NULL | NULL | 3 | 34 | 4 | +------+----------+------+------+-----------+ 3 rows in set (0.000 sec)