• 周四. 12 月 12th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

MySQL常用操作

admin

11 月 28, 2021

设置更改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 ~]# 

  

发表回复