第二十四次课 常用MySQL操作（一）
目录
一、设置更改root密码
二、连接mysql
三、mysql常用命令
四、mysql用户管理
五、常用sql语句
六、mysql数据库备份恢复
七、扩展
一、设置更改root密码
因为编译安装时指定的mysql安装目录在/usr/local/mysql, 不在环境变量PATH内，所以需要设置PATH，否则不能直接调用Mysql.
//修改/etc/profile文件，在文件末尾添加
[root@localhost mysql]# vim /etc/profile
//文件末尾添加下行内容
export PATH=/usr/local/mysql/bin:$PATH
//使配置文件生效
[root@localhost mysql]# source !$
source /etc/profile
//验证
[root@localhost mysql]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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管理员帐户root是没有密码的。所以需要给管理员帐户root设置一个密码。
//为root用户设置密码
root@localhost ~]# mysqladmin -uroot password '123456'
Warning: Using a password on the command line interface can be insecure.
//重启mysql服务使新密码生效
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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密码，过程如下：
//更改mysql登录密码，立即生效
[root@localhost ~]# mysqladmin -uroot -p'123456' password 'Aa123456'
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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的使用过程中也可能会碰到mysql密码被遗忘的情况，解决方法如下：
//编辑mysql的配置文件，
[root@localhost ~]# vim /etc/my.cnf
...略...
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
//增加此内容
skip-grant
# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql
datadir = /data/mysql
# port = .....
# server_id = .....
# socket = .....
...略...
//重启mysql服务器，并登录
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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数据库的user表中
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.02 sec)
mysql> select user,password from user where user='root';
+------+-------------------------------------------+
| user | password |
+------+-------------------------------------------+
| root | *4A488726AE5A0B0F0DB967998EE12D87F25C9610 |
| root | |
| root | |
| root | |
+------+-------------------------------------------+
4 rows in set (0.00 sec)
//更新密码为123456
mysql> update user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
//删除my.conf中的skip-grant
vim /etc/my.conf
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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
mysql主要有4种连接方式
1.mysql -uroot -p123456
[root@localhost ~]# mysql -uroot -p'123456'
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 4
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> quit
Bye
2.mysql -uroot -p123456 -h127.0.0.1 -P3306
//ip和端口连接方式
[root@localhost ~]# netstat -ntlup | grep :3306
tcp6 0 0 :::3306 :::* LISTEN 2664/mysqld
[root@localhost ~]# mysql -uroot -p'123456' -h 127.0.0.1 -P 3306
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 5
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> exit
Bye
3.mysql -uroot -p123456 -S/tmp/mysql.sock
//socket方式，其实与第一种是一样的
[root@localhost ~]# ps aux | grep mysql | grep socket
mysql 2923 13.2 45.0 1296312 450836 pts/1 Sl 22:46 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/localhost.localdomain.err --pid-file=/data/mysql/localhost.localdomain.pid --socket=/tmp/mysql.sock
[root@localhost ~]# mysql -uroot -p'123456' -S '/tmp/mysql.sock'
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.6.36 Source distribution
Copyright (c) 2000, 2017, 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> quit
Bye
4.mysql -uroot -p123456 -e sql_command
//这种方式可以直接在命令行上执行sql查询，可以用在bash脚本的编写中
[root@localhost ~]# mysql -uroot -p'123456' -e 'select user,password from mysql.user'
Warning: Using a password on the command line interface can be insecure.
+------+-------------------------------------------+
| user | password |
+------+-------------------------------------------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| | |
| | |
+------+-------------------------------------------+
三、mysql常用命令
查询库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
切换数据库
//切换库结尾可以不带;号
mysql> use mysql;
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_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
查看表的结构
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.01 sec)
创建数据库
mysql> create database dbtest;
Query OK, 1 row affected (0.01 sec)
创建在数据库中创建表
//注意是反引号
mysql> create table tb1(`id` int(4),`name` char(40));
Query OK, 0 rows affected (0.07 sec)
查看建表语句
// G代表横向显示
//如需建表时设定字符集，可以在create table语句后加上‘ENGINE=InnoDB DEFAULT CHARSET=utf8’，utf-8替换为相应的字符集
mysql> show create table tb1G
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前数据库
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.01 sec)
查看数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)
查看数据库状态
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 1011 |
| Bytes_sent | 21949 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 5 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 2 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 28 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 1 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 6 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 3 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 41 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 38 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 170 |
| Innodb_buffer_pool_bytes_data | 2785280 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 17 |
| Innodb_buffer_pool_pages_free | 8021 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 696 |
| Innodb_buffer_pool_reads | 167 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 47 |
| Innodb_data_fsyncs | 17 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2805760 |
| Innodb_data_reads | 182 |
| Innodb_data_writes | 18 |
| Innodb_data_written | 564224 |
| Innodb_dblwr_pages_written | 17 |
| Innodb_dblwr_writes | 2 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 8 |
| Innodb_log_writes | 4 |
| Innodb_os_log_fsyncs | 7 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 5632 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 4 |
| Innodb_pages_read | 166 |
| Innodb_pages_written | 17 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Innodb_num_open_files | 9 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6698 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 1 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 46 |
| Open_streams | 0 |
| Open_table_definitions | 81 |
| Open_tables | 81 |
| Opened_files | 167 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 13 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 103 |
| Questions | 43 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 3 |
| Slave_heartbeat_period | |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | |
| Slave_retried_transactions | |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 71 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 28 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
| Uptime | 2550 |
| Uptime_since_flush_status | 2550 |
+-----------------------------------------------+-------------+
341 rows in set (0.01 sec)
查看数据库参数
mysql> show variablesG
*************************** 1. row ***************************
Variable_name: auto_increment_increment
Value: 1
*************************** 2. row ***************************
Variable_name: auto_increment_offset
Value: 1
*************************** 3. row ***************************
Variable_name: autocommit
Value: ON
*************************** 4. row ***************************
Variable_name: automatic_sp_privileges
Value: ON
...中间略...
*************************** 448. row ***************************
Variable_name: version
Value: 5.6.36
*************************** 449. row ***************************
Variable_name: version_comment
Value: Source distribution
*************************** 450. row ***************************
Variable_name: version_compile_machine
Value: x86_64
*************************** 451. row ***************************
Variable_name: version_compile_os
Value: Linux
*************************** 452. row ***************************
Variable_name: wait_timeout
Value: 28800
*************************** 453. row ***************************
Variable_name: warning_count
Value: 0
453 rows in set (0.00 sec)
查看具体的数据库参数
//%号和like结合使用，%是通配符
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.02 sec)
临时设定参数
mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
//退出后重启mysql服务，参数失效
mysql> quit
Bye
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@localhost ~]# mysql -uroot -p'123456'
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 1
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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 variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.02 sec)
mysql>
永久修改参数值
[root@localhost ~]# vim /etc/my.cnf
//在[mysqld]下添加相应的参数
max_connect_errors=1000
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.^[[A^[[A SUCCESS!
[root@localhost ~]# mysql -uroot -p'123456'
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 1
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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 variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.01 sec)
查看队列
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
//查看完整进程队列
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
四、mysql用户管理
mysql安装完以后默认只有一个root管理用户，最高权限。不利于安全管理。需要根据需要创建不同用户。
//创建用户并授予全部权限
mysql> grant all on *.* to 'user01' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
//用新帐户登录
[root@localhost ~]# mysql -uuser01 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'user01'@'localhost' (using password: YES)
//系统中存在空帐户导致，删除多余空帐户
mysql> select User,Password from user;
+--------+-------------------------------------------+
| User | Password |
+--------+-------------------------------------------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| | |
| | |
| user01 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> delete from user where user='';
Query OK, 2 rows affected (0.00 sec)
mysql> select User,Password from user;
+--------+-------------------------------------------+
| User | Password |
+--------+-------------------------------------------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| user01 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-------------------------------------------+
5 rows in set (0.01 sec)
//该句一定要执行
mysql> FLUSH PRIVILEGES;
mysql> exit
Bye
[root@localhost ~]# mysql -u user01 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 19
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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.
//授予部分权限
//授予user08用户mysql数据库，user表查询，更新和插入的权限
mysql> grant SELECT,UPDATE,INSERT on mysql.user to 'user08' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> use mysql
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 database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
//因为对user表有权限，其他表都看不到
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user |
+-----------------+
1 row in set (0.00 sec)
//限制可以从哪个ip登录访问
//@后的部分可以指定可以访问的来源，%表示任意地址
//注意@前后的引号
mysql> grant all on *.* to 'user09'@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uuser09 -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 49
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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.
[root@localhost ~]# mysql -uuser09 -p -hlocalhost
Enter password:
ERROR 1045 (28000): Access denied for user 'user09'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uuser09 -p -h10.0.1.241
Enter password:
ERROR 1045 (28000): Access denied for user 'user09'@'10.0.1.241' (using password: YES)
//重新授权
mysql> show grants for user09@127.0.0.1;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for user09@127.0.0.1 |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user09'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user09'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uuser09 -p123456 -h127.0.0.1
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 62
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> exit
Bye
[root@localhost ~]# mysql -uuser09 -p123456 -hlocalhost
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 63
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> exit
Bye
[root@localhost ~]# mysql -uuser09 -p123456 -h10.0.1.241
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 64
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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 grants;
+----------------------------------------------------------------------------------------------------------------+
| Grants for user09@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user09'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select user();
+-------------------+
| user() |
+-------------------+
| user09@10.0.1.241 |
+-------------------+
1 row in set (0.00 sec)
//显示某用户的授权
mysql> show grants for 'user06'@'%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for user06@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user06'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
五、常用sql语句
1.查询
//查询语句select
//count()函数统计行数
//mysql.user指定mysql数据库，user表
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.00 sec)
mysql> select * from mysql.dbG
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> select db from db;
+---------+
| db |
+---------+
| test |
| test\_% |
+---------+
2 rows in set (0.00 sec)
mysql> select db,user from db;
+---------+------+
| db | user |
+---------+------+
| test | |
| test\_% | |
+---------+------+
2 rows in set (0.00 sec)
mysql> select user from mysql.user where user like 'user%';
+--------+
| user |
+--------+
| user01 |
| user02 |
| user03 |
| user06 |
| user08 |
| user09 |
| user09 |
+--------+
2.插入
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbtest |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use dbtest;
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_dbtest |
+------------------+
| tb1 |
+------------------+
1 row in set (0.01 sec)
mysql> desc db1;
ERROR 1146 (42S02): Table 'dbtest.db1' doesn't exist
mysql> desc tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
//插入数据
mysql> insert into tb1 values(1,'long');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb1 values(2,'oooo');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb1 values(3,'ccc');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | long |
| 2 | oooo |
| 3 | ccc |
+------+------+
3 rows in set (0.00 sec)
3.更新
//这里更新mysql数据库user表user08用户的密码
mysql> use mysql;
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> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.01 sec)
mysql> select user,password from user where user='user08';
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| user08 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> update user set password=password('Aa123456') where user='user08';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user,password from user where user='user08';
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| user08 | *4A488726AE5A0B0F0DB967998EE12D87F25C9610 |
+--------+-------------------------------------------+
1 row in set (0.02 sec)
4.删除表中的记录和删除整张表
//以dbtest数据库为例
mysql> use dbtest;
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_dbtest |
+------------------+
| tb1 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | long |
| 2 | oooo |
| 3 | ccc |
+------+------+
3 rows in set (0.00 sec)
//删除表中的一条记录
mysql> delete from tb1 where id=1;
Query OK, 1 row affected (0.02 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 2 | oooo |
| 3 | ccc |
+------+------+
2 rows in set (0.00 sec)
//删除整张表中的记录，但是表还在。
mysql> truncate table tb1;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from tb1;
Empty set (0.00 sec)
mysql> desc tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
//删除表，删除数据库都是极危险的操作。尽量少用，删除前一定要做备份
mysql> drop table tb1;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> drop database dbtest;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
六、mysql数据库备份恢复
小规模数据库备份工具mysqldump,mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。
备份数据库
//以mysql数据库为例
//备份单个数据库
[root@localhost ~]# mysqldump -uroot -p123456 mysql >/tmp/mysql.sql
[root@localhost ~]# head -n 10 /tmp/mysql.sql
-- MySQL dump 10.13 Distrib 5.6.36, for Linux (x86_64)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 5.6.36
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
恢复数据库
[root@localhost ~]# mysql -uroot -p123456 -e "create database mysql2"
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql2 |
| performance_schema |
| test |
+--------------------+
//恢复
[root@localhost ~]# mysql -uroot -p123456 mysql2 </tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456
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 80
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> use mysql2
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_mysql2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql> use mysql;
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_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
备份表
[root@localhost ~]# mysqldump -uroot -p123456 mysql2 user>/tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# head /tmp/user.sql
-- MySQL dump 10.13 Distrib 5.6.36, for Linux (x86_64)
--
-- Host: localhost Database: mysql2
-- ------------------------------------------------------
-- Server version 5.6.36
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
恢复表
mysql> use mysql2
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_mysql2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+
27 rows in set (0.00 sec)
//恢复表
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456 mysql2 </tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456
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 84
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> use mysql2
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_mysql2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.01 sec)
备份所有的数据库
[root@localhost ~]# mysqldump -uroot -p123456 -A > /tmp/all_in_one.sql
[root@localhost ~]# head /tmp/all_in_one.sql
-- MySQL dump 10.13 Distrib 5.6.36, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.6.36
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
仅备份表结构
[root@localhost ~]# mysqldump -uroot -p123456 -d mysql>/tmp/tab_structure.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# head /tmp/tab_structure.sql
-- MySQL dump 10.13 Distrib 5.6.36, for Linux (x86_64)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 5.6.36
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。
Xtrabackup优点
1）备份速度快，物理备份可靠
2）备份过程不会打断正在执行的事务（无需锁表）
3）能够基于压缩等功能节约磁盘空间和流量
4）自动备份校验
5）还原速度快
6）可以流传将备份传输到另外一台机器上
7）在不增加服务器负载的情况备份数据
Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup
1）xtrabackup 是专门用来备份InnoDB表的，和mysql server没有交互；
2）innobackupex 是一个封装xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。
3）xbcrypt 加密解密备份工具
4）xbstream 流传打包传输工具，类似tar
备份原理
备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log
安装xtrabackup
[root@localhost ~]# rpm -ivh https://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
Retrieving https://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:percona-release-0.1-4 ################################# [100%]
[root@localhost ~]# yum -y install percona-xtrabackup
Loaded plugins: fastestmirror
base | 3.6 kB 00:00:00
epel/x86_64/metalink | 5.1 kB 00:00:00
...中间略...
Installed:
percona-xtrabackup.x86_64 0:2.3.10-1.el7
Dependency Installed:
libaio.x86_64 0:0.3.109-13.el7 libev.x86_64 0:4.15-7.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-DBI.x86_64 0:1.627-4.el7
perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7
Complete!
完全备份
//创建backup用户
[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> grant reload,lock tables,replication client on *.* to 'dbbackup'@@@'localhost' identified by 'Aa123456';
Query OK, 0 rows affected (0.05 sec)
//完全备份
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=dbbackup --password='Aa123456' -S /tmp/mysql.sock /backup
180712 21:44:41 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!".
...中间略...
80712 21:44:45 [00] Writing xtrabackup_info
180712 21:44:45 [00] ...done
xtrabackup: Transaction log of lsn (1677199) to (1677199) was copied.
180712 21:44:45 completed OK!
//备份完成后,会在指定的保存目录中生成一个时间戳目录
[root@localhost ~]# ls /backup/
2018-07-12_21-44-41
完全备份的恢复
//模拟误删除数据库
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# mv /data/mysql /data/mysql.bak
[root@localhost ~]# ls -l /data/
total 0
drwxr-xr-x. 6 mysql mysql 173 Jul 12 21:48 mysql.bak
[root@localhost ~]# mkdir /data/mysql
//恢复
[root@localhost ~]# ls /backup/
2018-07-12_21-44-41
[root@localhost ~]# innobackupex --use-memory=512M --apply-log /backup/2018-07-12_21-44-41/
180712 21:57:52 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
...中间略...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1677334
180712 21:57:58 completed OK!
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/2018-07-12_21-44-41/
180712 21:59:45 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
...中间略...
180712 21:59:49 [01] ...done
180712 21:59:49 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
180712 21:59:49 [01] ...done
180712 21:59:49 completed OK!
//如果在恢复前，建目录时就设定该权限，恢复后会无法启动mysql
//错误：Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/localhost.localdomain.
[root@localhost ~]# chown -R mysql:mysql /data/mysql
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
增量备份
//增量备份需要搭配完全备份来实现完整备份和恢复。因为此前已经进行过数据的完全备份，这里不再重复
//模拟数据库变化
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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 dbbackup_test;
Query OK, 1 row affected (0.00 sec)
mysql> use dbbackup_test;
Database changed
mysql> create table dbbackup_tb1(`id` int(4),`name` char(20));
Query OK, 0 rows affected (0.05 sec)
//第一次增量备份
[root@localhost ~]# ls /backup/
2018-07-12_21-44-41
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=dbbackup --password='Aa123456' -S /tmp/mysql.sock --incremental /backup --incremental-basedir=/backup/2018-07-12_21-44-41/
180712 22:39:36 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!".
...中间略...
180712 22:39:42 [00] Writing xtrabackup_info
180712 22:39:42 [00] ...done
xtrabackup: Transaction log of lsn (1681471) to (1681471) was copied.
180712 22:39:42 completed OK!
[root@localhost ~]# ls /backup
2018-07-12_21-44-41 2018-07-12_22-39-36
//第二次增量备份
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbbackup_test |
| mysql |
| mysql2 |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.03 sec)
mysql> use dbbackup_test;
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> insert into dbbackup_tb1 values(1,'long');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dbbackup_tb1 values(1,'long');
Query OK, 1 row affected (0.03 sec)
mysql> insert into dbbackup_tb1 values(2,'qqq');
Query OK, 1 row affected (0.01 sec)
mysql> insert into dbbackup_tb1 values(3,'cccc');
Query OK, 1 row affected (0.01 sec)
mysql> delete from dbbackup_tb1 where id='1';
Query OK, 2 rows affected (0.01 sec)
mysql> exit
Bye
[root@localhost ~]# ls /backup/
2018-07-12_21-44-41 2018-07-12_22-39-36
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=dbbackup --password='Aa123456' -S /tmp/mysql.sock --incremental /backup --incremental-basedir=/backup/2018-07-12_22-39-36/
180712 22:50:30 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!".
...中间略...
180712 22:50:35 [00] ...done
180712 22:50:35 [00] Writing xtrabackup_info
180712 22:50:35 [00] ...done
xtrabackup: Transaction log of lsn (1691089) to (1691089) was copied.
180712 22:50:35 completed OK!
[root@localhost ~]# ls /backup/
2018-07-12_21-44-41 2018-07-12_22-39-36 2018-07-12_22-50-30
[root@localhost ~]# cat /backup/2018-07-12_21-44-41/
backup-my.cnf ib_logfile1 performance_schema/ xtrabackup_info
ibdata1 mysql/ test/ xtrabackup_logfile
ib_logfile0 mysql2/ xtrabackup_checkpoints
[root@localhost ~]# cat /backup/2018-07-12_21-44-41/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 1677199
last_lsn = 1677199
compact = 0
recover_binlog_info = 0
[root@localhost ~]# cat /backup/2018-07-12_22-39-36/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1677199
to_lsn = 1681471
last_lsn = 1681471
compact = 0
recover_binlog_info = 0
[root@localhost ~]# cat /backup/2018-07-12_22-50-30/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1681471
to_lsn = 1691089
last_lsn = 1691089
compact = 0
recover_binlog_info = 0
恢复测试
//模拟故障
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
//删除mysql原有数据
[root@localhost ~]# mv /data/mysql /data/mysql.bak
mv: overwrite ‘/data/mysql.bak/mysql’? n
[root@localhost ~]# mv /data/mysql /data/mysql.bak.1
[root@localhost ~]# ls /data/
mysql.bak mysql.bak.1
[root@localhost ~]# mkdir /data/mysql
//初始化全量
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2018-07-12_21-44-41
180712 23:04:06 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
xtrabackup: cd to /backup/2018-07-12_21-44-41/
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1677334
180712 23:04:06 completed OK!
//初始化第一次增量
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2018-07-12_21-44-41 --incremental-dir=/backup/2018-07-12_22-39-36
180712 23:05:30 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
...中间略...
180712 23:05:33 [00] Copying /backup/2018-07-12_22-39-36//xtrabackup_info to ./xtrabackup_info
180712 23:05:33 [00] ...done
180712 23:05:33 completed OK!
//初始化第二次增量
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2018-07-12_21-44-41 --incremental-dir=/backup/2018-07-12_22-50-30
180712 23:07:48 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
...中间略...
chema/session_account_connect_attrs.frm
180712 23:07:50 [01] ...done
180712 23:07:50 [00] Copying /backup/2018-07-12_22-50-30//xtrabackup_info to ./xtrabackup_info
180712 23:07:50 [00] ...done
180712 23:07:50 completed OK!
//
[root@localhost ~]# innobackupex --copy-back /backup/2018-07-12_21-44-41
180712 23:12:12 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
180712 23:12:12 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0
180712 23:12:13 [01] ...done
180712 23:12:14 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1
180712 23:12:16 [01] ...done
180712 23:12:16 [01] Copying ibdata1 to /data/mysql/ibdata1
180712 23:12:16 [01] ...done
...中间略...
180712 23:12:19 [01] ...done
180712 23:12:19 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
180712 23:12:19 [01] ...done
180712 23:12:19 completed OK!
[root@localhost ~]# chown -R mysql:mysql /data/mysql
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
七、扩展
mysql5.7 root密码更改
http://www.apelearn.com/bbs/thread-7289-1-1.html
myisam 和innodb引擎对比
http://www.pureweber.com/article/myisam-vs-innodb/
mysql 配置详解：
http://blog.linuxeye.com/379.html
mysql调优：
http://www.aminglinux.com/bbs/thread-5758-1-1.html
同学分享的亲身mysql调优经历：
http://www.apelearn.com/bbs/thread-11281-1-1.html
SQL语句教程
http://www.runoob.com/sql/sql-tutorial.html
什么是事务？事务的特性有哪些？
http://blog.csdn.net/yenange/article/details/7556094
根据binlog恢复指定时间段的数据
https://blog.csdn.net/lilongsy/article/details/74726002
mysql字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013
参考：