MySQL常用操作
设置更改root密码
1.查看mysql是否启动
[root@localhost ~]# ps aux|grep mysql
[root@localhost ~]# /etc/init.d/mysqld start
2.不能直接用mysql命令，因为没有将mysql命令添加到环境变量中，给mysql添加环境变量
[root@localhost ~]# mysql -uroot bash: mysql: 未找到命令... [root@localhost ~]#
[root@localhost ~]# ls /usr/local/mysql/bin/mysql /usr/local/mysql/bin/mysql [root@localhost ~]#
[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin/ [root@localhost ~]#
3.mysql命令可以用了
[root@localhost ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
4.将mysql添加到开机启动选项中，环境变量永久生效
[root@localhost ~]# vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin/
5.让刚添加的配置立即生效
[root@localhost ~]# source /etc/profile [root@localhost ~]#
6.登录mysql
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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
7.给root用户设置密码
[root@localhost ~]# mysqladmin -uroot password 'root' Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
8.修改mysql的root用户密码为admin888
[root@localhost ~]# mysqladmin -uroot -p'root' password 'admin888' [root@localhost ~]# mysql -uroot -p'admin888' 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 11 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
9.不知道root的密码情况修改root密码，编辑/etc/my.conf，添加skip-grant，意思是不用密码了可以直接登录
[root@localhost mariadb]# vim /etc/my.cnf [root@localhost mariadb]#
[mysqld]
skip-grant
datadir=/data/mysql
socket=/tmp/mysql.sock
10.重启mysql服务，再次用root用户登录，不需要密码了
[root@localhost mariadb]# /etc/init.d/mysqld restart Shutting down MySQL... SUCCESS! Starting MySQL.... SUCCESS! [root@localhost mariadb]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
11.还需要修改root用户所在的表，use mysql 切换到mysql库，sql执行修改root密码为root执行成功
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> update user set password=password('root') where user='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql>
12.打开myql配置文件去掉skip-grant，重启服务器
[root@localhost local]# vim /etc/my.cnf [root@localhost local]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL..... SUCCESS! [root@localhost local]# mysql -uroot -proot 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.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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
1.远程连接mysql -h + 远程ip地址
[root@localhost local]# mysql -uroot -proot -h127.0.0.1 -P3306 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.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
2.使用sockit连接mysql（只适合在本机连接mysql）
[root@localhost local]# mysql -uroot -proot -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 3 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
3.连接mysql后，执行一些命令（常用在shell脚本中）
[root@localhost local]# mysql -uroot -proot -e "show databases" Warning: Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ [root@localhost local]#
MySQL常用命令
1.常用命令
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
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>
mysql> show create table user\G; （查看建表语句 \G:竖着显示结果）
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> select user(); （查看当前登录用户） +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>
[root@localhost ~]# less .mysql_history （查看mysql命令历史） [root@localhost ~]#
mysql> select database(); （显示当前所用的数据库） +------------+ | database() | +------------+ | NULL | +------------+ 1 row 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> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) mysql>
1.创建数据库db1
mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql>
2.切换db1，创建t1表
mysql> use db1; Database changed mysql> create table t1(`id` int(4), `name` char(40)); Query OK, 0 rows affected (0.02 sec) mysql>
3.查看t1表结构
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
4.#号注释的意思，drop 删除表
mysql> #drop table t1;
5.默认指定字符集utf8
mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
6.字符集变成utf8了
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
7.查看数据库版本和状态
mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.35 | +-----------+ 1 row in set (0.00 sec) mysql> show status; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 2 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 1198 | | Bytes_sent | 22193 | | 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 | 2 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 1 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 2 | | 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 | 1 | | 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 | 6 | | 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 | 2 | | Com_show_create_trigger | 0 | | Com_show_databases | 3 | | 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 | 2 | | 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 | 10 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 5 | | 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 | 47 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 42 | | Innodb_buffer_pool_dump_status | not started | | Innodb_buffer_pool_load_status | not started | | Innodb_buffer_pool_pages_data | 181 | | Innodb_buffer_pool_bytes_data | 2965504 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 54 | | Innodb_buffer_pool_pages_free | 8010 | | 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 | 944 | | Innodb_buffer_pool_reads | 168 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 189 | | Innodb_data_fsyncs | 38 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2822144 | | Innodb_data_reads | 183 | | Innodb_data_writes | 80 | | Innodb_data_written | 1791488 | | Innodb_dblwr_pages_written | 54 | | Innodb_dblwr_writes | 4 | | Innodb_have_atomic_builtins | ON | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 34 | | Innodb_log_writes | 11 | | Innodb_os_log_fsyncs | 16 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 19456 | | Innodb_page_size | 16384 | | Innodb_pages_created | 14 | | Innodb_pages_read | 167 | | Innodb_pages_written | 54 | | 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 | 169 | | Opened_table_definitions | 4 | | Opened_tables | 2 | | 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 | 1031352 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 15 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | | Queries | 100 | | Questions | 48 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 5 | | 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 | 70 | | Table_locks_waited | 0 | | Table_open_cache_hits | 28 | | Table_open_cache_misses | 2 | | 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 | 3635 | | Uptime_since_flush_status | 3635 | +-----------------------------------------------+-------------+ 341 rows in set (0.03 sec) mysql>
8.查看mysql参数
mysql> show variables\G;
mysql> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec) mysql>
mysql> show variables like 'slow%'; +---------------------+--------------------------------+ | Variable_name | Value | +---------------------+--------------------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /data/mysql/localhost-slow.log | +---------------------+--------------------------------+ 3 rows in set (0.00 sec) mysql> set global max_connect_errors=1000; （临时修改配置文件错误日志为1000） Query OK, 0 rows affected (0.00 sec) mysql>
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql>
9.永久修改配置文件错误日志生效需要修改mysql配置文件，添加 max_connect_errors
[root@localhost ~]# vim /etc/my.cnf [root@localhost ~]#
mysql> show processlist;（查看队列） +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 10 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) mysql>
mysql> show full processlist; （查看完整队列） +----+------+-----------+------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+-----------------------+ | 10 | root | localhost | NULL | Query | 0 | init | show full processlist | +----+------+-----------+------+---------+------+-------+-----------------------+ 1 row in set (0.00 sec) mysql>
MySQL用户管理
一个MySQL数据库里可以跑多个库，总不能给所有人的程序员root用户，则可以给他们单独的用户访问数据库。
1.grant是授权，all是所有权限（数据的查看、创建、删除等），第一个*表示库名（如果写成mysql.*就是针对mysql库中所有的表有权限），第二个*表示表名，user1是用户名，127.0.0.0.1（也可以写成%同配表示所有ip都可以）是用户只能通过这个ip登录mysql服务器，identified by 指定密码
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'admin888'; Query OK, 0 rows affected (0.23 sec) mysql>
2.第一次登录不成功（默认mysql用的是sock连接，所以必须要加-h指定ip，如果不想指定可以把来源ip换成localhost，localhost就是针对的sock。）
[root@bogon ~]# mysql -uuser1 -padmin888 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES) [root@bogon ~]#
3.指定-h后登录成功（grant这种语句是不会记录到命令历史里去的，因为不安全。）
[root@bogon ~]# mysql -uuser1 -padmin888 -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 17 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
3.show grants 查看用户所有的授权（如果是root用户登录的情况需要指定用户 show grants for user1@'127.0.0.1';）
mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------+ | Grants for user1@127.0.0.1 | +-----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*C9A034778FB438CDECD150408139516FEBC8FA5B' | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.25 sec) mysql>
mysql> show grants for user1@'127.0.0.1'; +-----------------------------------------------------------------------------------------------------------------------+ | Grants for user1@127.0.0.1 | +-----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*C9A034778FB438CDECD150408139516FEBC8FA5B' | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) mysql>
4.给user2添加授权（把之前的all换成权限则表示不把所有的权限给他，只给他指定的权限。）
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; Query OK, 0 rows affected (0.03 sec) mysql>
5.查看user2的授权
mysql> show grants for user2@'192.168.133.1'; +------------------------------------------------------------------------------------------------------------------+ | Grants for user2@192.168.133.1 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
6.grants命令是可以添加授权的，比如192.168.133.1是来源ip，我想把192.168.133.2也设置为来源ip，可以复制grants的2个字符串，把ip改成192.168.133.2，则可以直接和192.168.133.1的密码一样，授权一样。
常用sql语句
常见的数据库引擎有2种，
MyISAM和InooDB
MyISAM的特点是会自动帮你统计行数，所以你用下面的命令select count(*)命令就会很快显示出来。
InooDB如果要用select count(*)或select *时就很慢。
所以尽量减少select count(*)和select *这种查询所有的操作。
1.查询一个表的总记录数
mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) mysql>
2.查看表结构并查询表中db字段的数据
mysql> show create table db\G;
*************************** 1. row ***************************
Table: db
Create Table: CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select db from db;
+---------+
| db |
+---------+
| test |
| test\_% |
| db1 |
+---------+
3 rows in set (0.00 sec)
mysql>
3.查询两个字段
mysql> select db,user from db; +---------+-------+ | db | user | +---------+-------+ | test | | | test\_% | | | db1 | user2 | +---------+-------+ 3 rows in set (0.00 sec) mysql>
4.用like模糊匹配查询
mysql> select * from db where host like '192.168.133.%'\G;
*************************** 1. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.02 sec)
ERROR:
No query specified
mysql>
5.插入数据
mysql> desc db1.t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | char(40) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> select * from db1.t1; Empty set (0.00 sec) mysql> insert into db1.t1 values (1,'abc'); Query OK, 1 row affected (0.00 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) mysql>
6.修改数据
mysql> update db1.t1 set name='aaa' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec) mysql>
7.删除数据
mysql> delete from db1.t1 where id=1; Query OK, 1 row affected (0.02 sec) mysql> select * from db1.t1; Empty set (0.00 sec) mysql>
8.truncate清空一个表
mysql> insert into db1.t1 values (1,'abc'); Query OK, 1 row affected (0.03 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) mysql> truncate db1.t1; Query OK, 0 rows affected (0.02 sec) mysql> select * from db1.t1; Empty set (0.00 sec) mysql>
9.drop删除一个表或一个库
mysql> drop table db1.t1; Query OK, 0 rows affected (0.00 sec) mysql> select * from db1.t1; ERROR 1146 (42S02): Table 'db1.t1' doesn't exist mysql>
mysql> drop database db1; Query OK, 0 rows affected (0.00 sec) mysql>
MySQL数据库备份恢复
1.备份库mysql库
[root@bogon ~]# mysqldump -uroot -proot mysql > /tmp/mysqlbak.sql Warning: Using a password on the command line interface can be insecure. [root@bogon ~]#
2.创建新库并恢复mysql库到mysql2库中
[root@bogon ~]# mysql -uroot -proot -e 'create database mysql2'
[root@bogon ~]# mysql -uroot -proot mysql2 < /tmp/mysqlbak.sql Warning: Using a password on the command line interface can be insecure. [root@bogon ~]#
3.直接进入mysql2库中
[root@bogon ~]# mysql -uroot -proot mysql2 Warning: Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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 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>
mysql> select database(); +------------+ | database() | +------------+ | mysql2 | +------------+ 1 row in set (0.00 sec) mysql>
4.备份mysql库中的user表
[root@bogon ~]# mysqldump -uroot -proot mysql user > /tmp/user.sql Warning: Using a password on the command line interface can be insecure. [root@bogon ~]#
[root@bogon ~]# less /tmp/user.sql [root@bogon ~]#
5.恢复一个表到mysql2中的user表
[root@bogon ~]# mysql -uroot -proot mysql2 < /tmp/user.sql Warning: Using a password on the command line interface can be insecure. [root@bogon ~]#
6.备份所有的库-A
[root@bogon ~]# mysqldump -uroot -proot -A > /tmp/mysql_all.sql Warning: Using a password on the command line interface can be insecure. [root@bogon ~]#
7.只备份表结构-d选项
[root@bogon ~]# mysqldump -uroot -proot -d mysql2 > /tmp/mysql2.sql Warning: Using a password on the command line interface can be insecure. [root@bogon ~]#
