设置更改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 userG; (查看建表语句 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 t1G; *************************** 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 t1G;
*************************** 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 variablesG;
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 [email protected] | +-----------------------------------------------------------------------------------------------------------------------+ | 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 [email protected] | +-----------------------------------------------------------------------------------------------------------------------+ | 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 [email protected] | +------------------------------------------------------------------------------------------------------------------+ | 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 dbG; *************************** 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 ~]#