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