1、设置密码前的配置
首先查看mysql是否启动，未启动就启动它。
[root@zyshanlinux-001 ~]# ps aux |grep mysql root 1703 0.0 0.0 112720 972 pts/0 R+ 21:43 0:00 grep --color=auto mysql [root@zyshanlinux-001 ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! [root@zyshanlinux-001 ~]# !ps ps aux |grep mysql root 1722 0.0 0.0 113308 1628 pts/0 S 21:43 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/zyshanlinux-001.pid mysql 1858 1.7 24.1 973096 453512 pts/0 Sl 21:43 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/zyshanlinux-001.err --pid-file=/data/mysql/zyshanlinux-001.pid --socket=/tmp/mysql.sock root 1894 0.0 0.0 112720 972 pts/0 R+ 21:44 0:00 grep --color=auto mysql
启动mysql -uroot命令失败原因：没在环境变量PATH中，需对PATH更改才能免绝对路径运行该命令。
[root@zyshanlinux-001 ~]# mysql -uroot -bash: mysql: 未找到命令 [root@zyshanlinux-001 ~]# ls /usr/local/mysql/bin/mysql /usr/local/mysql/bin/mysql [root@zyshanlinux-001 ~]# echo $PATH /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@zyshanlinux-001 ~]# export PATH=$PATH:/usr/local/mysql/bin/ ##临时的更改
运行mysql -uroot命令成功
[root@zyshanlinux-001 ~]# 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> quit Bye
如果想这个变量永久的生效，要把export PATH=$PATH:/usr/local/mysql/bin/编辑到/etc/profile最后一行去就可以。
[root@zyshanlinux-001 ~]# vi /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.
pathmunge () {
case ":${PATH}:" in
*:"$1":*)
;;
*)
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
esac
}
if [ -x /usr/bin/id ]; then
if [ -z "$EUID" ]; then
# ksh workaround
EUID=`/usr/bin/id -u`
UID=`/usr/bin/id -ru`
fi
USER="`/usr/bin/id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
fi
# Path manipulation
if [ "$EUID" = "0" ]; then
pathmunge /usr/sbin
pathmunge /usr/local/sbin
else
pathmunge /usr/local/sbin after
pathmunge /usr/sbin after
fi
HOSTNAME=`/usr/bin/hostname 2>/dev/null`
HISTSIZE=2000
HISTTIMEFORMAT="%Y/%m/%d %H:%M:%S"
if [ "$HISTCONTROL" = "ignorespace" ] ; then
export HISTCONTROL=ignoreboth
else
export HISTCONTROL=ignoredups
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL
# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 199 ] && [ "`/usr/bin/id -gn`" = "`/usr/bin/id -un`" ]; then
umask 002
else
umask 022
fi
for i in /etc/profile.d/*.sh ; do
if [ -r "$i" ]; then
if [ "${-#*i}" != "$-" ]; then
. "$i"
else
. "$i" >/dev/null
fi
fi
done
unset i
unset -f pathmunge
export PATH=$PATH:/usr/local/mysql/bin/ ##在最后加上这行，该变量就永久生效。
如果之前没有单独运行了export PATH=$PATH:/usr/local/mysql/bin/这条命令，仅仅是写入到/etc/profile末行，还不能运行mysql -uroot命令，因为变量还没生效，需要运行命令source /etc/profile
[root@zyshanlinux-001 ~]# source /etc/profile
2、更改密码步骤
mysql默认是没有密码的，这时可以不用加-p参数指定密码；加-p也可以，在跳出的密码栏输入回车就进入了mysql。
[root@zyshanlinux-001 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 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 Bye
设置初始密码，设置密码成功，由于密码是可见的会提示不安全。然后登录mysql，就必须输入你先确设置的密码。
[root@zyshanlinux-001 ~]# mysqladmin -uroot password 'mysql01' Warning: Using a password on the command line interface can be insecure.
更改密码，必须用新密码才能登录；密码最好用单括号括起来，密码中可能有特殊符号。没有特殊符号的话可以不用加单括号。
[root@zyshanlinux-001 ~]# mysqladmin -uroot -p'mysql01' password 'mysql02' Warning: Using a password on the command line interface can be insecure. [root@zyshanlinux-001 ~]# mysql -uroot -pmysql02 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 24 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>
不知道密码的情况下：打开配置文件，加入skip-grant这行，取消授权。
[root@zyshanlinux-001 ~]# vi /etc/my.cnf [mysqld] skip-grant ##加这一句，取消授权，即不用输入密码了。 datadir=/data/mysql socket=/tmp/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld_safe] #log-error=/var/log/mariadb/mariadb.log #pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # #!includedir /etc/my.cnf.d
更改配置后需要重启服务，不需要密码就可以进入mysql，进入mysql库，更改保存用密码的user表。
[root@zyshanlinux-001 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@zyshanlinux-001 ~]# mysql -uroot 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> use mysql; ##切换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 * from user; ##保存用户密码数据的user表，需要更改。
更改命令：update user set password=password('mysql01') where user='root';前后加密密码四行的变化。
mysql> select password from user;
+-------------------------------------------+
| password |
+-------------------------------------------+
| *A4EFC41236280F16A8C4CEF573E512045725B8E9 |
| *A4EFC41236280F16A8C4CEF573E512045725B8E9 |
| *A4EFC41236280F16A8C4CEF573E512045725B8E9 |
| *A4EFC41236280F16A8C4CEF573E512045725B8E9 |
| |
| |
+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> update user set password=password('mysql01') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select password from user;
+-------------------------------------------+
| password |
+-------------------------------------------+
| *4512F73DF9A45D6AFA3887D36BDF8C1E3F5BB439 |
| *4512F73DF9A45D6AFA3887D36BDF8C1E3F5BB439 |
| *4512F73DF9A45D6AFA3887D36BDF8C1E3F5BB439 |
| *4512F73DF9A45D6AFA3887D36BDF8C1E3F5BB439 |
| |
| |
+-------------------------------------------+
6 rows in set (0.00 sec)
mysql>
密码更改完成，还需要把配置文件/etc/my.cnf改回去，把取消授权这行skip-grant删除掉，重启mysqld服务
[root@zyshanlinux-001 ~]# vi /etc/my.cnf [root@zyshanlinux-001 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
连接MySQL
常用的连接mysql的命令
1、连接本机
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 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> quit Bye
2、连接远程服务器的mysql
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 -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 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> quit Bye
3、只适合在本机的
[root@zyshanlinux-001 ~]# ps aux |grep mysql root 1275 0.0 0.0 115428 1708 ? S 20:12 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/zyshanlinux-001.pid mysql 1412 1.0 24.1 1038896 452616 ? Sl 20:12 0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/zyshanlinux-001.err --pid-file=/data/mysql/zyshanlinux-001.pid --socket=/tmp/mysql.sock root 1647 0.0 0.0 112720 968 pts/0 R+ 20:15 0:00 grep --color=auto mysql [root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 -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 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 Bye
4、连接Mysql后操作的一些命令（适合使用在shell脚本里）
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 -e "show databases" Warning: Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
总结：选项-u和-p与选项-S一样的（虽然第1种没有指定IP和端口，它就默认用sock去连接。），选项-h和-P指定远程Mysql数据库的IP和端口，选项-e去执行一些命令。
MySQL常用命令
注意：在mysql运用的命令不要与linux的命令弄混，两者是不通用的；mysql的命令需要在命令的结尾加上分号“;”
1、查看有什么数据库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
2、切换到哪一个库下面
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
3、查看库里的表
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)
4、查看表里的字段
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.00 sec)
5、查看创建表语句（“\G”竖排显示）
mysql> show create table user\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.01 sec)
ERROR:
No query specified
6、查看当前用户
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 -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 9 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ [root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 -h192.168.106.128 ##不写端口，就默认是3306端口 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 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> select user(); +----------------------+ | user() | +----------------------+ | root@zyshanlinux-001 | ##用IP192.168.106.128登录会反解析root@zyshanlinux-001 +----------------------+ 1 row in set (0.00 sec)
7、查看当前使用的数据库
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> use mysql; Database changed mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
8、创建库create database db1;
mysql> create database db1; Query OK, 1 row affected (0.27 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
9、创建表create table t1(`id` int(4), `name` char(40));
mysql> use db1;
Database changed
mysql> create table t1(`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.36 sec)
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> drop table t1; ##删除表
Query OK, 0 rows affected (0.09 sec)
mysql> #create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##在命令前面加“#”是取消当前命令。
mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##可以在创建表的时候添加格式字段参数。
Query OK, 0 rows affected (0.02 sec)
mysql>
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
10、查看当前数据库版本
mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.35 | +-----------+ 1 row in set (0.00 sec)
11、查看数据库状态
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 | 1289 |
12、查看各参数
mysql> show variables; mysql> show variables like 'slow%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /data/mysql/zyshanlinux-001-slow.log | +---------------------+--------------------------------------+ 3 rows in set (0.00 sec)
13、修改参数，内存上改的；如果想永久更改，就需要到配置文件上改。[root@zyshanlinux-001 ~]# vim /etc/my.cnf
mysql> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.01 sec) mysql> set global max_connect_errors=1000; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
14、查看队列，带full在最后一列是显示完整的信息。
mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 11 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) mysql> show full processlist; +----+------+-----------+------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+-----------------------+ | 11 | root | localhost | NULL | Query | 0 | init | show full processlist | +----+------+-----------+------+---------+------+-------+-----------------------+ 1 row in set (0.00 sec)
MySQL用户管理
创建用户grant all授权全部，第一个*可以写某个库，@后面指定IP
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'user1'; Query OK, 0 rows affected (0.28 sec)
只能通过指定IP才能登录，默认sock登录失败。
[root@zyshanlinux-001 ~]# mysql -uuser1 -puser1 -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 12 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 Bye [root@zyshanlinux-001 ~]# mysql -uuser1 -puser1 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
如果默认sock也能登录，就要进入root用户把user1用户的127.0.0.1更改“localhost”，就可以。
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 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 15 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> grant all on *.* to 'user1'@'localhost' identified by 'user1'; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye [root@zyshanlinux-001 ~]# mysql -uuser1 -puser1 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 16 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>
在db1库中授予SELECT,UPDATE,INSERT这三个权限给user2来源IP192.168.133.1，指定密码。
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'user2'; Query OK, 0 rows affected (0.00 sec) 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 '*12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
在db1库中授予全部权限给user3来源IP所有（%代表所有）
grant all on db1.* to 'user3'@'%' identified by 'passwd';
查看指定用户的授权是什么
mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +-----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*34D3B87A652E7F0D1D371C3DBF28E291705468C4' | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) 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 '*34D3B87A652E7F0D1D371C3DBF28E291705468C4' | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
当用户'user2'IP'192.168.133.1'有这三个权限后，192.168.133.2也想要这3个权限。可以把use2的授权状态再分别执行一次，只需要更改IP为2就好。
+------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1' | +------------------------------------------------------------------------------------------------------------------+ mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*12A20BE57AF67CBF230D55FD33FBAF5230CCFDBC4'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2' -> ; Query OK, 0 rows affected (0.00 sec) mysql> show grants for user2@192.168.133.2; +------------------------------------------------------------------------------------------------------------------+ | Grants for user2@192.168.133.2 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
常用sql语句
尽量少用*和count(*)
1、查找mysql库的user表的行数“count(*)”
mysql> use db1; 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 count(*) from mysql.user; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.01 sec)
2、查看所有的内容
mysql> select * from mysql.db\G; *************************** 1. row *************************** Host: % Db: test User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y
3、查看2个3个字段
mysql> select db from mysql.db; +---------+ | db | +---------+ | test | | test\_% | | db1 | | db1 | +---------+ 4 rows in set (0.00 sec) mysql> select db,user from mysql.db; +---------+-------+ | db | user | +---------+-------+ | test | | | test\_% | | | db1 | user2 | | db1 | user2 | +---------+-------+ 4 rows in set (0.00 sec)
4、模糊匹配(like就是模糊)
mysql> select * from mysql.db where host like '192.168.%'\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
*************************** 2. row ***************************
Host: 192.168.133.2
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
2 rows in set (0.00 sec)
ERROR:
No query specified
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.00 sec) mysql> select * from db1.t1; Empty set (0.01 sec) 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> insert into db1.t1 values (1, '123'); Query OK, 1 row affected (0.00 sec) mysql> insert into db1.t1 values (1, '456'); Query OK, 1 row affected (0.00 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | | 1 | 123 | | 1 | 456 | +------+------+ 3 rows in set (0.00 sec)
6、更改
mysql> update db1.t1 set name='aaa' where id=1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | | 1 | aaa | | 1 | aaa | +------+------+ 3 rows in set (0.00 sec) mysql> update db1.t1 set id=2 where name='aaa'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 2 | aaa | | 2 | aaa | | 2 | aaa | +------+------+ 3 rows in set (0.00 sec) mysql> delete from db1.t1 where id=2; ##删除 Query OK, 3 rows affected (0.01 sec) mysql> select * from db1.t1; ##确认删完 Empty set (0.00 sec)
7、清空一个表的内容，表的结构还在。
mysql> truncate table db1.t1; Query OK, 0 rows affected (0.07 sec) mysql> select * from db1.t1; Empty set (0.00 sec) 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.00 sec)
8、删除表格，表的内容结构都删除。
mysql> drop table db1.t1; Query OK, 0 rows affected (0.01 sec)
9、把库也删除了。
mysql> drop database db1; Query OK, 0 rows affected (0.00 sec)
MySQL数据库备份恢复
1、备份库，mysqldump是备份工具
[root@zyshanlinux-001 ~]# mysqldump -uroot -pmysql01 mysql > /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.
2、恢复库到新建的mysql2上
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 -e "create database mysql2" Warning: Using a password on the command line interface can be insecure. [root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 mysql2 < /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.
确认恢复到位
mysql> use mysql; mysql> show tables; mysql> use mysql2; mysql> show tables;
3、备份表，只要在库的后面空格加上表
[root@zyshanlinux-001 ~]# mysqldump -uuser1 -puser1 mysql user > /tmp/user.sql Warning: Using a password on the command line interface can be insecure.
4、恢复表，把表恢复到mysql2去
[root@zyshanlinux-001 ~]# mysql -uroot -pmysql01 mysql2 < /tmp/user.sql Warning: Using a password on the command line interface can be insecure.
5、备份所有的库
[root@zyshanlinux-001 ~]# mysqldump -uroot -p -A >/tmp/123.sql [root@zyshanlinux-001 ~]# mysqldump -uroot -pmysql01 -A >/tmp/mysql_all.sql Warning: Using a password on the command line interface can be insecure.
6、只备份表结构
[root@zyshanlinux-001 ~]# mysqldump -uroot -pmysql01 -d mysql2 > /tmp/mysql2.sql Warning: Using a password on the command line interface can be insecure. [root@zyshanlinux-001 ~]# less !$ less /tmp/mysql2.sql
拓展：
使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql相关视频 链接：http://pan.baidu.com/s/1miFpS9M 密码：86dx 链接：http://pan.baidu.com/s/1o7GXBBW 密码：ue2f
扩展 mysql5.7 root密码更改 http://www.apelearn.com/bbs/thread-7289-1-1.htmlmyisam 和innodb引擎对比 http://www.pureweber.com/article/myisam-vs-innodb/mysql 配置详解： http://blog.linuxeye.com/379.htmlmysql调优： http://www.aminglinux.com/bbs/thread-5758-1-1.html同学分享的亲身mysql调优经历： http://www.apelearn.com/bbs/thread-11281-1-1.htmlSQL语句教程 http://www.runoob.com/sql/sql-tutorial.html什么是事务？事务的特性有哪些？ http://blog.csdn.net/yenange/article/details/7556094根据binlog恢复指定时间段的数据 https://blog.csdn.net/lilongsy/article/details/74726002mysql字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013
462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
