Linux运维之MySQL数据库基础服务详解

程序员文章站 2022-06-24 20:47:26

第四部分数据库服务第三十章 MySQL基础一、MySQL单实例源码cmake方式编译安装 1、安装cmake软件（1）解压cmake tar zxf cmake-2...

第四部分数据库服务

第三十章 MySQL基础

一、MySQL单实例源码cmake方式编译安装

1、安装cmake软件

（1）解压cmake

tar zxf cmake-2.8.8.tar.gz

cd cmake-2.8.8

（2）configure

./configure

（3）gmake

gmake

（4）gmake install

gmake install

cd ..

2、确认依赖包是否安装

rpm -qa | grep ncurses

ncurses-5.7-4.20090207.el6.x86_64

ncurses-base-5.7-4.20090207.el6.x86_64

ncurses-term-5.7-4.20090207.el6.x86_64

ncurses-libs-5.7-4.20090207.el6.x86_64

ncurses-devel-5.7-4.20090207.el6.x86_64

如没有安装，则安装之

yum install ncurses-devel -y

3、安装MySQL

（1）解压MySQL

tar xf mysql-5.5.32.tar.gz

cd mysql-5.5.32

（2）创建mysql用户和组

useradd mysql -s /sbin/nologin -M

id mysql

（3）configure

a、不指定字符集，使用默认拉丁字符集

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \

-DMYSQL_DATADIR=/application/mysql-5.5.32/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FAST_MUTEXES=1 \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0

b、指定字符集为utf8

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

（4）make && make install

make && make install

（5）创建MySQL软连接

ln -s /application/mysql-5.5.32/ /application/mysql

（6）配置环境变量

echo 'export PATH=/application/mysql/bin/:$PATH' >>/etc/profile

（7）检查环境变量

a、查看profile

tail -1 /etc/profile

export PATH=/application/mysql/bin/:$PATH

b、让配置生效

source /etc/profile

c、查看PATH

echo $PATH

/application/mysql/bin/:/application/nginx/sbin:/application/nginx/sbin:/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

（8）检查数据目录是否创建

ll -d /application/mysql/data/

drwxr-xr-x 3 root root 4096 Mar 26 22:10 /application/mysql/data/

（9）授权mysql用户访问数据目录

chown -R mysql.mysql /application/mysql/data/

（10）调整/tmp目录权限（否则初始化会报错）

chmod 1777 /tmp/

（11）创建MySQL配置文件:my.conf

cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf

（12）初始化MySQL

cd /application/mysql/scripts/

./mysql_install_db --user=mysql --basedir=/application/mysql/--datadir=/application/mysql/data/

（13）创建MySQL启动脚本

cp/application/mysql/support-files/mysql.server /etc/init.d/mysqld

（14）增加MySQL启动脚本的可执行权限

chmod +x /etc/init.d/mysqld

（15）启动MySQL

/etc/init.d/mysqld start

（16）为root用户指定密码

/application/mysql/bin/mysqladmin -u rootpassword '123456'

（17）登陆MySQL

mysql -uroot -p

（18）删除无用的数据库和用户

delete from mysql.user where host='::1'or user='';

drop database test;

（19）额外添加管理员

grant all privileges on *.* tosystem@'localhost' identified by '123456' with grant option

（20）配置MySQL启动级别

chkconfig mysqld on

chkconfig --list mysqld

mysqld 0:off 1:off2:on 3:on 4:on5:on 6:off

二、MySQL多实例概念

1、什么是多实例

就是一台服务器上开启多个不同的服务端口，来运行多个MySQL服务里程，通过不用同socket 监听不同的服务端口提供各自的服务。

这些MySQL多实例，共用一套MySQL安装程序，使用不同（也可相同）的my.conf配置文件、启动程序、数据文件。在提供服务时，多实例的MySQL在逻辑上看起来是各自独立的，多个实例根据配置文件对应的设定值，获取相应的服务器资源。

2、多实例的作用、问题和应用场景

（1）作用

a、有效利用服务器资源

b、节约服务器资源

c、节省IDC机柜

（2）问题

a、资源互相抢占

b、当某个服务实例并发很高或有慢查询时导致其它实例性能下降

（3）应用场景

a、资金紧张的公司

b、并发访问不是特别大的业务

c、门户网站应用较多

百度搜索引擎的数据库是多实例，一般是从库（48核，96G，3-4个实例）

sina网也是多实例（内存48G）

3、MySQL多实常见配置方案

（1）多配置文件及多个启动程序（推荐方法）

tree data

/data

├── 3306

│ ├── data #3306实例的数据文件

│ ├── my.cnf #3306实例的配置文件

│ └── mysql #3306实例的启动文件

└── 3307

├── data

├── my.cnf

└── mysql

（2）单一配置文件部署方案

a、my.cnf配置文件样例（mysql手册中提到的方法）

[mysqld_multi]

mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user = mysql

[mysqld1]

socket =/var/lib/mysql/mysql.sock

port =3306

pid-file = /var/lib/mysql/mysql.pid

datadir =/var/lib/mysql/

user =mysql

[mysqld2]

socket =/mnt/data/db2/mysql.sock

port =3303

pid-file = /mnt/data/db2/mysql.pid

datadir =/mnt/data/db2/

user =mysql

skip-name-resolve

server-id = 11

master-connect-retry = 60

default-storage-engine = innodb

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool = 10M

default_character_set = utf8

character_set_server = utf8

#read-only

relay-log-space-limit =3G

expire_logs_day =20

[mysqld3]

socket =/mnt/data/db1/mysql.sock

port =3302

pid-file = /mnt/data/db1/mysql.pid

datadir =/mnt/data/db1/

user =mysql

skip-name-resolve

server-id = 10

default-storage-engine = innodb

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool = 10M

default_character_set = utf8

character_set_server = utf8

#read-only

relay-log-space-limit =3G

expire_logs_day =20

b、启动方法

mysqld_multi--config-file=/data/mysql/my_multi.cnf start 1,2,3

c、停止方法

mysqld_multi stop 1,3

d、存在的问题

耦合性太强

三、MySQL多实例安装：多配置文件、启动程序方法

1、安装依赖包

yum install ncurses-devel -y

yum install libaio-devel -y

2、cmake方式编译安装MySQL单实例源码

整个安装过程只到make install和建立软链接就停止，后面的步骤不用操作，多实例方式需重新配置。

3、创建MySQL多实例的数据文件目录

（1）关闭单实例MySQL的所有进程

pkill mysqld

ps -ef | grep mysql

（2）删除MySQL启动命令

rm -f /etc/init.d/mysqld

（3）创建多实例目录

mkdir -p /data/{3306,3307}/data

4、创建多实例配置文件

（1）配置3306的配置文件

vi /data/3306/my.cnf

[client]

port = 3306

socket =/data/3306/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user = mysql

port = 3306

socket =/data/3306/mysql.sock

basedir = /application/mysql

datadir = /data/3306/data

open_files_limit = 1024

back_log = 600

max_connections = 800

max_connect_errors = 3000

table_cache = 614

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 100

thread_concurrency = 2

query_cache_size = 2M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

long_query_time = 1

#log_long_format

#log-error = /data/3306/error.log

#log-slow-queries = /data/3306/slow.log

pid-file = /data/3306/mysql.pid

log-bin = /data/3306/mysql-bin

relay-log = /data/3306/relay-bin

relay-log-info-file = /data/3306/relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days = 7

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

#myisam_sort_buffer_size = 1M

#myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 4M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data/3306/mysql_oldboy3306.err

pid-file=/data/3306/mysqld.pid

（2）配置3307的配置文件

vi /data/3307/my.cnf

[client]

port = 3307

socket =/data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user = mysql

port = 3307

socket =/data/3307/mysql.sock

basedir = /application/mysql

datadir = /data/3307/data

open_files_limit = 1024

back_log = 600

max_connections = 800

max_connect_errors = 3000

table_cache = 614

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 100

thread_concurrency = 2

query_cache_size = 2M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

#long_query_time = 1

#log_long_format

#log-error = /data/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file = /data/3307/mysql.pid

#log-bin = /data/3307/mysql-bin

relay-log = /data/3307/relay-bin

relay-log-info-file = /data/3307/relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days = 7

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

#myisam_sort_buffer_size = 1M

#myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 3

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 4M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data/3307/mysql_oldboy3307.err

pid-file=/data/3307/mysqld.pid

5、创建多实例启动文件

（1）多实例启动/停止MySQL服务的实质

当启动MySQL时，如果配置文件不在/etc下的话，在启动时，必须通过--defaults-file参数来指定MySQL的配置文件my.cnf的路径

启动：

mysqld_safe--default-file=/data/3306/my.cnf 2>&1 >/dev/null &

mysqld_safe--default-file=/data/3307/my.cnf 2>&1 >/dev/null &

停止（平滑停止MySQL）：

mysqladmin -uroot -p 123456 -S /data/3306/mysql.sock shutdown

mysqladmin -uroot -p 123456 -S /data/3307/mysql.sock shutdown

（2）配置3306的启动文件

vi /data/3306/mysql

#!/bin/sh

#init

port=3306

mysql_user="root"

mysql_pwd="oldboy"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

#startup function

function_start_mysql()

{

if [ ! -e"$mysql_sock" ];then

printf "StartingMySQL...\n"

/bin/sh${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &

else

printf "MySQL isrunning...\n"

exit

}

#stop function

function_stop_mysql()

{

if [ ! -e"$mysql_sock" ];then

printf "MySQL isstopped...\n"

exit

else

printf "StopingMySQL...\n"

${CmdPath}/mysqladmin-u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

printf "RestartingMySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

printf "Usage:/data/${port}/mysql {start|stop|restart}\n"

esac

（3）配置3307的启动文件

vi /data/3307/mysql

#!/bin/sh

#init

port=3307

mysql_user="root"

mysql_pwd="oldboy"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

#startup function

function_start_mysql()

{

if [ ! -e"$mysql_sock" ];then

printf "StartingMySQL...\n"

/bin/sh${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &

else

printf "MySQL isrunning...\n"

exit

}

#stop function

function_stop_mysql()

{

if [ ! -e "$mysql_sock"];then

printf "MySQL isstopped...\n"

exit

else

printf "StopingMySQL...\n"

${CmdPath}/mysqladmin-u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

printf "RestartingMySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

printf "Usage:/data/${port}/mysql {start|stop|restart}\n"

esac

6、授权mysql用户管理/data目录

chown -R mysql.mysql /data

7、给予mysql脚本执行权限

find /data -type f -name mysql | xargs chmod +x

8、配置环境变量

echo 'export PATH=/application/mysql/bin/:$PATH' >>/etc/profile

9、初始化多实例MySQL

（1）MySQL5.1.X

#命令在mysql/bin目录下

mysql_install_db --basedir=/application/mysql--datadir=/data/3306/data --user=mysql

mysql_install_db --basedir=/application/mysql--datadir=/data/3307/data --user=mysql

（2）MySQL5.5.X

#命令在mysql/scripts目录下

cd /application/mysql/scripts

./mysql_install_db--basedir=/application/mysql --datadir=/data/3306/data --user=mysql

./mysql_install_db--basedir=/application/mysql --datadir=/data/3307/data --user=mysql

（3）比较一下：单实例MySQL5.5.X

mysql_install_db --user=mysql

10、启动多实例MySQL

/data/3306/mysql start

/data/3307/mysql start

11、查看是否启动成功：检查端口和进程

（1）检查端口

netstat -lntup| grep 330[6-7]

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 14756/mysqld

tcp 0 00.0.0.0:3307 0.0.0.0:* LISTEN24286/mysqld

（2）检查进程

ps -ef | grep mysql

12、修改MySQL用户密码：必须指定sock

mysqladmin -u root -S /data/3306/mysql.sock password '123456'

mysqladmin -u root -S /data/3307/mysql.sock password '123456'

13、MySQL多实例数据库安装排错方法

（1）如果没有显示MySQL的端口，要等几秒再看，MySQL服务启动相对于Web有些慢

（2）如果还不行，则要查看MySQL的错误日志

grep log-error /data/3306/my.cnf

#log-error = /data/3306/error.log

log-error=/data/3306/mysql_oldboy3306.err

（3）细看所有执行命令返回的屏幕输出，不要忽略关键的输出内容

（4）查看系统的/var/message

（5）如果是关联服务，要同时查看相关服务的LOG

14、登陆MySQL多实例数据库

（1）MySQL多实例登陆必须通过-S参数指定sock（远程是IP）才能登陆

mysql -S /data/3306/mysql.sock

create database d3306;

mysql -S /data/3307/mysql.sock

create database d3307;

（2）也可能在一个MySQL客户端中利用system函数调用系统命令来进入另个实例

mysql -S /data/3306/mysql.sock

system mysql -S /data/3307/mysql.sock

show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| d3307 |

| mysql |

| performance_schema |

| test |

15、关闭MySQL多实例数据库

在关闭MySQL多实例数据库时，是需要密码的，所以在/data/{port}/mysql中是要写入MySQL用户密码的。一定要与真实密码相匹配，否则无法关闭数据库。同时，由于密码放在启动脚本中，存在安全问题，所以要把启动脚本文件的权限改为700.

（1）指定启动脚本中的密码

grep mysql_pwd /data/3306/mysql

mysql_pwd="123456"

（2）停止数据库

/data/3306/mysql stop

/data/3307/mysql stop

（3）修改启动脚本的权限

find /data -type f -name mysql | xargs chmod 700

find /data -type f -name mysql | xargs chown root.root

或

find /data -type f -name mysql -exec chmod 700 {} \;

find /data -type f -name mysql -exec chown root.root {} \;

（4）查看改变后的启动脚本权限

find /data -type f -name mysql -exec ls -l {} \;

-rwx------ 1 root root 1307 Mar 28 13:36 /data/3306/mysql

-rwx------ 1 root root 1307 Mar 28 13:36 /data/3307/mysql

16、重启MySQL多实例数据库

（1）先关闭数据库

/data/3306/mysql stop

/data/3307/mysql stop

（2）再启动数据库

/data/3306/mysql start

/data/3307/mysql start

17、远程登陆MySQL多实例数据库

本地连接MySQL数据库，需要指定sock文件，远程连接时，只需指定IP和端口

mysql -uroot -p123456 -h 192.168.1.3 -P 3307

四、新增一个MySQL实例：端口3308

mkdir -p /data/3308/data

2、拷贝/data/3306目录下的my.cnf和mysql文件到3308

cp /data/3306/my.cnf /data/3308/

cp /data/3306/mysql /data/3308/

3、将3308目录下的my.cnf和mysql中的3306改为3308

sed -n s#3306#3308#gp my.cnf

sed -i s#3306#3308#g my.cnf

sed -n s#3306#3308#gp mysql

sed -i s#3306#3308#g mysql

4、修改my.cnf中的server-id

server-id = 5

5、授权mysql用户访问3308目录

chown -R mysql.mysql 3308/

6、初始化数据库3308

cd /application/mysql/scripts/

./mysql_install_db --basedir=/application/mysql --datadir=/data/3308/data--user=mysql

7、启动3308数据库

/data/3308/mysql start

8、修改MySQL用户密码：必须指定sock

mysqladmin -u root -S /data/3308/mysql.sock password '123456'

9、登陆MySQL-3308

mysql -S /data/3308/mysql.sock -uroot -p

create database d3308;

五、MySQL管理基础

1、MySQL启动基本原理

/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld服务启动MySQL。/etc/init.d/mysqld脚本中调用mysqld_safe的内容如下：

$bindir/mysqld_safe--datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args>/dev/null 2>&1 &

2、MySQL单实例启动命令

/etc/init.d/mysqld start

3、查看MySQL端口：CentOS7取消了netstart命令，改为ss，两者一样

ss -lnt | grep 3306

4、查看MySQL进程

ps -ef | grep mysql | grep -v grep

5、利用初始化数据库时MySQL提示的方法启动

（1）启动命令

mysqld_safe --user=mysql

（2）提示说明

a、当需找回root密码时，经常用mysql_safe --user=mysql &带参数启动。

b、自己载发脚本启动MySQL数据库时，可能会使用到这个启动方法

c、/etc/init.d/mysqld和mysql_safe --user=mysql &的启动实质是一样的。

6、关闭数据库方法

（1）常见正常（优雅）方法

a、mysqladmin方法

mysqladmin -uroot -p123456 shutdown

b、自带脚本

/etc/init.d/mysqld stop

c、kill信号的方法（最好不用）

kill -USR2 `cat path/pid`

（2）强制关闭数据库

killall mysqld

pkill mysqld

killall -9 mysqld

（3）强制关闭数据库的注意事项

a、用killall关闭，要直到出现mysqld: no process killed才表示完成关闭操作

b、一般在生产环境尽量不要用强制关闭数据库的方法，在生产环境中，高并发业务时，可能会引起数据丢失。甚至导致数据库无法启动的故障。

野蛮粗鲁杀死数据库导致故障企业案例见备注参考博文19

7、登陆MySQL

（1）单实例登陆

mysql -uroot -p

（2）多实例登陆

a、本地登陆

mysql -S /data/3308/mysql.sock -uroot -p

b、远程登陆

mysql -uroot -p -h 192.168.1.3 -P3306

（3）MySQL操作命令的历史记录

cat /root/.mysql_history

8、修改MySQL登陆提示符

（1）一般显示的提示符

mysql>

（2）命令行临时修改提示符

mysql> prompt \u@mysql \r:\m:\s>

PROMPT set to '\u@mysql \r:\m:\s>'

root@mysql 02:18:11>

（3）my.cnf配置文件永久修改提示符

[mysql] #不是[mysqld]

prompt=\\u@mysql \\r:\\m:\\s>

9、善用MySQL的帮助命令help

所有操作在登陆MySQL后进行。

（1）显示客户端相关命令帮助：help

mysql> help

（2）显示MySQL命令帮助：help command

mysql> help show

mysql> help grant

10、退出MySQL

Bye

quit

exit

ctrl + c

ctrl + d

11、设置及修改MySQL root用户密码

（1）新增root权限的用户system

grant all privileges on *.* to system@'localhost' identified by '123456'with grant option;

（2）删除除system外的所有用户

delete from mysql.user where user<>'system'

（3）为root用户设置密码（命令行操作，刚安装时root没密码）

a、单实例

mysqladmin -u root password '123456';

b、多实例

mysqladmin -u root -S /data/3307/mysql.sock password '123456'

（4）修改root用户密码（命令行操作）

a、方法一：mysqladmin命令行操作

（i）单实例

mysqladmin -u root -p123456 password '123456789';

（ii）多实例

mysqladmin -u root -p123456 -S /data/3307/mysql.sock password '123456'

b、方法二：普通SQL操作

（i）用法

select user,host,password from mysql.user;

update mysql.user set password=password('456') where user='root' andhost='localhost';

flush privileges;

（ii）应用场景

适合于MySQL root用户密码丢失后通过--skip-grant-tables参数启动数据库后修改密码。

c、方法三：MySQL的set命令-修改当前登陆用户的密码

mysql> set password=password('123456789');

mysql> flush privileges;

12、找回丢失的MySQL的root用户密码-单实例

（1）停止MySQL数据库

/etc/init.d/mysqld stop

（2）使用--skip-grant-tables参数忽略密码验证模式启动数据库

mysqld_safe -–skip-grant-tables -–user=mysql &

（3）直接登陆MySQL

mysql

或

mysql –uroot –p #直接回车，无密码

（4）修改root用户密码

a、不能用mysqladmin方式修改密码，因为mysqladmin在用户存在密码时修改密码，是要求提供原密码的。

b、只能使用SQL语句来修改

update mysql.user set password=password("456") whereuser='root' and host='localhost';

flush privileges;

quit;

（5）重启MySQL数据库

a、不能用/etc/init.d/mysqldstop来停止数据库，因为不是脚本启动的，没有PID文件

b、只能使用mysqladmin命令加修改后的密码来停止数据库

mysqladmin –uroot –p456 shutdown

ps –ef | grep mysql

c、使用/etc/init.d/mysqld脚本来启动数据库

/etc/init.d/mysqld start

（6）客户端登陆MySQL数据库

mysql –uroot –p456

13、找回丢失的MySQL的root用户密码-多实例

（1）停止数据库

killall mysqld #只能用killall了，尽量不要用kill -9

（2）使用skip-grant-tables和defaults-file参数忽略密码验证模式和指定my.cnf文件启动数据库

mysqld_safe –-defaults-file=/data/3306/my.cnf -–skip-grant-tables -–user=mysql&

说明：-–defaults-file必须在-–skip-grant-tables

（3）直接登陆MySQL

mysql –S /data/3306/mysql.sock

（4）修改root用户密码

update mysql.user set password=password("456") whereuser='root' and host='localhost';

flush privileges;

quit;

（5）重启MySQL数据库

killall mysqld

ps –ef | grep mysql

/data/3306/mysql start

（6）客户端登陆MySQL数据库

mysql –uroot –p456 –S /data/3306/mysql.sock

六、MySQL常见管理

1、创建数据库

（1）语法

create database

（2）要求

不能以数字开头

（3）示例：使用在编译安装时没有指定数据库字符集的MySQL数据库

a、创建一个默认字符集的数据库

（i）创建

create database test1;

（ii）查看数据库

show databases like 'test%';

（iii）查看建库语句

show create database test1;

+----------+------------------------------------------------------------------+

| Database | Create Database|

+----------+------------------------------------------------------------------+

| test1 | CREATE DATABASE`test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+------------------------------------------------------------------+

show create database test1\G

*************************** 1. row ***************************

Database: test1

Create Database: CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTERSET latin1 */

（iv）说明

拉丁（latin1）字符集实际上也可以支持中文

加\G的作用是来格式化输出信息，便于查看

在编译时没有指定数据库字符集，则默认是拉丁（latin1）字符集。

b、创建一个GBK字符集的数据库

（i）创建

create database test2 default character set gbk collategbk_chinese_ci;

（ii）查看数据库

show databases like 'test%';

（iii）查看建库语句

show create database test2\G

*************************** 1. row ***************************

Database: test2

Create Database: CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTERSET gbk */

（iv）说明

collate gbk_chinese_ci是校对规则

c、创建一个UTF8字符集的数据库

（i）创建

create database test3 default character set utf8 collate utf8_general_ci;

（ii）查看数据库

show databases like 'test%';

（iii）查看建库语句

show create database test3\G

*************************** 1. row ***************************

Database: test3

Create Database: CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTERSET utf8 */

d、生产企业是如何创建MySQL数据库

（i）根据开发的程序确定字符集，一般建议用UTF8

（ii）编译的时间指定字符集

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

（iii）建库的时候默认即可

create database

（iv）如果编译时没指定或指定了与程序不同的字符集，则在建库时指定字符集即可

create database test3 default character set utf8 collateutf8_general_ci;

（v）数据库必须支持指定字符集

示例：

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii

2、连接数据库

（1）语法

use

（2）示例

use test1;

3、查看数据库

（1）语法

select database();

（2）示例

a、如果没有使用use dbname，则显示为NULL

select database();

+------------+

| database() |

+------------+

| NULL |

+------------+

b、使用use dbname，则显示相应的dbname

use test1

Database changed

select database();

+------------+

| database() |

+------------+

| test1 |

+------------+

4、删除数据库

（1）语法

drop database

（2）示例

drop database test1;

5、查看数据库版本

select version();

+-----------+

| version() |

+-----------+

| 5.1.72 |

+------------+

6、查看当前用户

select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

7、查看当前时间

select user();

+---------------------+

| now() |

+---------------------+

| 2017-03-27 21:15:46 |

+---------------------+

8、查看当前数据库下的表

show tables;

Empty set (0.00 sec) #新建的库，没有表

9、查看指定数据库下的表

show tables from test2;

show tables in test2;

10、删除数据库中指定用户

（1）语法

drop user "user"@"主机域"

（2）要求

可以是单引号，也可以是双引号。但是不能不加。如果主机名中有大写字母的则drop删除不了，可以用delete语句来删除mysql.user表中的用户。

（3）示例

drop user 'root'@'localhost';

drop user 'system'@"localhost";

drop user ''@"localhost";

11、创建用户及授权

（1）查看GRANT命令帮助获取创建用户并授权的例子

mysql> help grant;

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';

GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITHMAX_QUERIES_PER_HOUR 90;

（2）普通创建用户方法：先Create后grant

a、语法

CREATE USER 'username'@'主机域' IDENTIFIED BY 'mypass';

GRANT ALL ON dbname.* to 'username'@'localhost'

b、示例

create user 'u1'@'localhost' identified by '123456';

grant all on test1.* to 'u1'@'localhost';

（3）运维人员常用创建用户方法：使用grant命令在创建用户的同时进行授权

a、语法

GRANT ALL PRIVILEGES ON dbname.* to 'username'@'主机域' identified by 'password';

b、说明

GRANT	ALL PRIVILEGES	ON dbname.*	to 'username'@'主机域'	identified by 'password'
授权命令	对应权限	目标：库和表	用户名和客户端主机	用户密码

（4）远程主机授权连接数据库

grant命令的语法中主机域部分为授权访问数据库的客户端主机，可以用域名、IP或IP段来表示。远程主机的授权方法有2种，具体如下：

a、方法一：百分号匹配法

grant all on *.* to test@'192.168.0.%' identified by '123456';

flush privileges;

b、方法二：子网掩码匹配法

grant all on *.* to test@'192.168.1.0/24'identified by '123456';

grant all on *.* to test@'192.168.1.0/255.255.255.0' identified by'123456';

flush privileges;

（5）示例

a、创建u1用户，具备test1库所有权限，并允许从localhost主机登陆数据库，密码是123456

（i）grant命令创建并授权

grant all on test1.* to 'u1'@'localhost' identified by '123456';

（ii）刷新权限

flush privileges;

（iii）查看权限

show grants for u1@'localhost';

+----------------------------------------------------------------------------------+

| Grants for u1@localhost |

+---------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO'u1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB743..' |

| GRANT ALL PRIVILEGES ON `test1`.* TO'u1'@'localhost' |

+----------------------------------------------------------------------------------+

（iv）说明

查看权限可以看出授权是分为2步：

第一步是授权可登陆GRANT USAGE ON*.*。USAGE表示只有登陆权限。

第二步是授权可访问ON`test1`.* TO 'u1'@'localhost'

b、创建u2用户，具备test1库所有权限，并允许从192.168.0.3主机登陆数据库，密码是123456

（i）grant命令创建并授权

grant all on test1.* to 'u2'@'192.168.0.3' identified by'123456';

（ii）刷新权限

flush privileges;

（iii）说明

如果授权时主机域不是客户端主机，则会报错，提示没有权限访问MySQL

Host'192.168.0.3 is not allowed to connect to this MySQL server

c、创建u3用户，测试USAGE权限

（i）创建u3用户，允许从localhost主机登陆数据库，密码是123456

create user u3@'localhost' identified by '123456';

（ii）查看权限

show grants for u3@'localhost';

+-----------------------------------------------------------------------------+

| Grants for u3@localhost|

+-----------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'u3'@'localhost' IDENTIFIED BYPASSWORD '*6310..。。。。' |

+-----------------------------------------------------------------------------+

（iii）登陆数据库

mysql -uu3 –p123456

（iv）查看数据库：没有权限，只能看到information_schema

show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

（v）创建一个新数据库：报错是，提示没有权限（Access denied）

create database test4;

ERROR 1044 (42000): Access denied for user 'u3'@'localhost' todatabase 'test4'

（vi）在mysql的root用户下对u3进行授权，具备test1库所有权限

mysql> mysql –uroot –p123456

grant all on test1.* to u3@'localhost';

flush privileges;

（vii）在mysql的u3用户查看数据库

show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test1 |

+--------------------+

d、创建u4用户，测试远程连接

（i）创建u4用户，授权具备所有权限，允许从192.168.0.0网段的所有主机登陆数据库，密码是123456

grant all on *.* to 'u4'@'192.168.0.%' identified by '123456';

（ii）刷新权限

flush privileges;

（iii）客户端登陆

mysql -uu4 -p123456 -h192.168.0.2

（iv）户查看数据库

show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| bbs |

| mysql |

| test1 |

| test2 |

| test3 |

+--------------------+

12、查看AllPRIVILEGES权限内容

（1）方法一：通过show grants查看

a、查看u1用户在test1数据库的权限：ALL PRIVILEGES

show grants for u1@'localhost';

+----------------------------------------------------------------------------+

| Grants for u1@localhost |

+---------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO'u1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837E..|

| GRANT ALL PRIVILEGES ON `test1`.* TO'u1'@'localhost' |

+---------------------------------------------------------------------------+

b、移除u1用户在test1数据库的INSERT权限：必段匹配相关数据库

revoke insert on test1 to 'u1'@'localhost'

c、再次查看u1用户在test1数据库的权限

show grants for u1@'localhost';

+---------------------------------------------------------------------------+

| Grants for u1@localhost|

+---------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO'u1'@'localhost' IDENTIFIED BY PASSWORD '*6B8....' |

| GRANT SELECT, UPDATE, DELETE, CREATE,DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY, TABLES, LOCK TABLES, EXECUTE,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON`test1`.* TO 'u1'@'localhost' |

+---------------------------------------------------------------------------+

d、myslq命令使用-e参数，通过Linux Shell命令行查看u1用户在test1数据库的权限

mysql -uroot -p123456 -e "show grants for u1@'localhost';"

e、MySQL权限列表如下

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,ALTER, CREATE TEMPORARY, TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

（2）方法二：通过MySQL相关表查看

a、与用户权限相关的表

（i）表名

mysql.db、mysql.user、mysql.host

（ii）字段含义

Select_priv：确定用户是否可以通过SELECT命令选择数据。

Insert_priv：确定用户是否可以通过INSERT命令插入数据。

Update_priv：确定用户是否可以通过UPDATE命令修改现有数据。

Delete_priv：确定用户是否可以通过DELETE命令删除现有数据。

Create_priv：确定用户是否可以创建新的数据库和表。

Drop_priv：确定用户是否可以删除现有数据库和表。

Reload_priv：确定用户是否可执行刷新和重新加载各种内部缓存的特定命令，包括日志、权限、主机、查询和表。

Shutdown_priv：确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时应当谨慎。

Process_priv：确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程。

File_priv：确定用户是否可以执行SELECT INTO OUTFILE和LOADDATA INFILE命令。

Grant_priv：确定用户是否可以将已经授予给该用户自己的权限再授予其他用户。

References_priv：目前只是某些未来功能的占位符；现在没有作用。

Index_priv：确定用户是否可以创建和删除表索引。

Alter_priv：确定用户是否可以重命名和修改表结构。

Show_db_priv：确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库。可以考虑对所有用户禁用这个权限，除非有特别不可抗拒的原因。

Super_priv：确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SETGLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令。

Create_tmp_table_priv：确定用户是否可以创建临时表。

Lock_tables_priv：确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改。

Execute_priv：确定用户是否可以执行存储过程。此权限只在MySQL 5.0及更高版本中有意义。

Repl_slave_priv：确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。

Repl_client_priv：确定用户是否可以确定复制从服务器和主服务器的位置。

Create_view_priv：确定用户是否可以创建视图。此权限只在MySQL 5.0及更高版本中有意义。

Show_view_priv：确定用户是否可以查看视图或了解视图如何执行。只在MySQL 5.0及更高版本中有意义。

Create_routine_priv：确定用户是否可以更改或放弃存储过程和函数。此权限是在MySQL 5.0中引入的。

Alter_routine_priv：确定用户是否可以修改或删除存储函数及函数。此权限是在MySQL 5.0中引入的。

Create_user_priv：确定用户是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户。

Event_priv：确定用户能否创建、修改和删除事件。这个权限是MySQL 5.1.6新增的。

Trigger_priv：确定用户能否创建和删除触发器，这个权限是MySQL 5.1.6新增的。

b、通过mysql.db表查看用户u1在数据库test上的权限:

select * from mysql.db where user='u1'\G;

*************************** 1. row ***************************

Host:localhost

Db: test

User: u1

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

c、通过mysql.user表查看用户u1权限

select * from mysql.user where user='u1'\G;

*************************** 1. row ***************************

Host:localhost

User: u1

Password:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string: NULL

13、企业生产环境如何给用户授权

（1）博客、BBS等产品的数据库授权

对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此在安装期间除了select、insert、update、delete这4个权限外，还需create、drop等比较危险的权限。安装完成后，还应将create、drop等权限收回。

a、授权

grant select, insert, update, delete, create, drop on blog.* to'blog'@'%' identified by '123456';

b、安装

c、收加权限

revoke create, drop on blog.* from 'blog'@'%'

14、查看MySQL变量和状态

（1）查看已生效的变量

show variables;

（2）查看MySQL当前的状态

show global status;

（3）不重启MySQL，让新增变量生效

a、设置变量临时生效

set global 变量=值

b、查看是否生效

show variables like '变量名%'

c、在配置文件my.cnf中设置相关变量，以便重启后生效

d、示例

set global key_buffer_size=8096

show variables like 'key_buffer%'

vi /etc/my.cnf

global key_buffer_size=8096M

（4）查看当前MySQL的连接情况

show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

+----+------+-----------+------+---------+------+-------+------------------+

+----+------+-----------+------+---------+------+-------+------------------+

（5）生产环境常用状态命令

a、查看当前会话的数据库状态信息

show status;

b、查看整个数据库运行状态信息（很重要，要分析并做好监控，DBA重点关注）

show global status;

c、查看正在执行的SQL语句（看不全）

show processlist;

d、查看正在执行的SQL语句（全）

show full processlist;

e、查看数据库的生效参数信息

show variables;

f、临时调整数据库参数（重启后失效，一般情况下关闭参数不能改，大小参数可以改）

set global key_buffer_size = 31*1024*1024

七、SQL操作

1、创建测试环境

（1）创建测试数据库mytest

create database mytest;

（2）查看数据库建库语句

show create database mytest\G;

*************************** 1. row ***************************

Database: mytest

Create Database: CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTERSET latin1 */

（3）使用测试库

use mytest;

2、创建表

（1）语法（Syntax）

a、通过帮助获取

mysql> help create table

b、常用格式

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <表名>(

<字段1><类型1>,

<字段2><类型2>,

...

<字段n><类型n>

);

（2）建表示例

a、手工建表语句

create table student (

id int(4) not null,

name char(20) not null,

age tinyint(2) not nulldefault '0', #很小的数字类型，比int小

dept varchar(16) defaultNULL

);

b、MySQL生成的建表语句

CREATE TABLE `student` (

`id` int(4) NOT NULL,

`name` char(20) NOT NULL,

`age` tinyint(2) NOT NULLDEFAULT '0',

`dept` varchar(16) DEFAULTNULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

c、查看建表语句

show create table student\G;

*************************** 1. row ***************************

Table: student

Create Table: CREATE TABLE `student` (

`id` int(4) NOT NULL,

`name` char(20) NOT NULL,

`age` tinyint(2) NOT NULLDEFAULT '0',

`dept` varchar(16) DEFAULTNULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

d、生产环境建表语句示例

某sns产品生产正式建表语句

use sns;

set names gbk;

CREATE TABLE `subject_comment_manager` (

`subject_comment_manager_id` bigint(12) NOT NULL auto_incrementCOMMENT '主键',

`subject_type` tinyint(2) NOT NULL COMMENT '素材类型',

`subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键',

`subject_title` varchar(255) NOT NULL COMMENT '素材的名称',

`edit_user_nick` varchar(64) default NULL COMMENT '修改人',

`edit_user_time` timestamp NULL default NULL COMMENT '修改时间',

`edit_comment` varchar(255) default NULL COMMENT '修改的理由',

`state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',

PRIMARY KEY(`subject_comment_manager_id`),

KEY `IDX_PRIMARYKEY`(`subject_primary_key`(32)),#括号内的32表示对前32个字符做前缀索引。

) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;

（3）查看表结构

a、方法一：desc

desc student;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO| | NULL ||

| name | char(20) | NO| | NULL ||

| age | tinyint(2) | NO| | 0 ||

| dept | varchar(16) |YES || NULL | |

+-------+-------------+------+-----+---------+-------+

b、方法二：show columns

mysql> show columns from student;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | NO| | NULL ||

| name | char(20) | NO| | NULL ||

| age | tinyint(2) | NO| | 0 ||

| dept | varchar(16) |YES || NULL | |

+-------+-------------+------+-----+---------+-------+

3、创建索引

（1）创建主键索引

a、方法一：在建表时，增加主键索引

（i）示例

create table student(

id int(4) not null AUTO_INCREMENT,

name char(20) not null,

age tinyint(2) NOT NULL default '0',

dept varchar(16) default NULL,

primary key(id), #在ID列增加主键索引

);

（ii）查看表结构

desc student;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO| PRI | NULL | auto_increment |

| age | tinyint(2) | NO| | 0 | |

| dept | varchar(16) |YES || NULL | |

+-------+-------------+------+-----+---------+----------------+

b、方法二：在建表后，通过alter命令增加主键索引

（i）示例-未建主键的Student2表

create table student2(

id int(4) not null,

name char(20) not null,

age tinyint(2) NOT NULLdefault '0',

dept varchar(16) defaultNULL,

KEY index_name (name)

);

（ii）增加主键

alter table student2 change id id int primary key;

desc student2;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO| PRI | NULL | |

| age | tinyint(2) | NO| | 0 ||

| dept | varchar(16) |YES || NULL | |

+-------+-------------+------+-----+---------+-------+

（2）创建普通索引

a、方法一：在建表时，增加主键索引

create table student3(

id int(4) not null AUTO_INCREMENT,

name char(20) not null,

age tinyint(2) NOT NULL default '0',

dept varchar(16) default NULL,

primary key(id), #在ID列增加主键索引

KEY index_name (name) #在name列增加普通索引

);

b、方法二：在建表后，通过alter命令增加普通索引

（i）语法（Syntax）

alter table add index (columnname);

（ii）示例

alter table student3 add index myIndex_student3_name(name);

alter table student3 add index myIndex_student3_dept(dept);

（3）删除索引

a、删除主键索引

alter table student2 drop primary key;

b、删除普通索引

alter table student3 drop index myIndex_student3_name;

drop index myIndex_student3_name on student;

（4）对列中前n个字符建立索引

a、语法（Syntax）

create index index_name on table_name(column_name(length));

b、示例

create index myStudentIndexName on student(dept(8));

（5）创建联合索引

a、语法（Syntax）

create index index_name on table_name(column_name1,column_name2);

b、示例

create index myStudentUnionIndexName on student(name,dept);

create index myStudentUnionIndexName on student(name(4),dept(8));

c、联合索引生效条件

按条件列查询数据时，联合索引是有前缀生效特性的。如

index(a, b, c)只有公a,ab, abc三个查询条件列才可能走索引。b,bc, ac, c等是无法走索引的。

（6）创建非主键的唯一索引

a、语法（Syntax）

create unique index index_name on table_name(column_name);

b、示例

create unique index myStudentUnionIndexName on student(age);

（7）查看索引

show index from student\G

（8）索引列的创建和生效条件

a、问一：既然索引可以加快查询速度，那么就给所有的列建索引吧？

解答：

索引会加快查询速度，但会影响更新速度。索引不仅占用系统空间，更新数据库时还需维护索引数据。因此，索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建索引，写频繁、读少的业务要少建索引。

b、到底在哪些列上创建索引？

解答：

（i）索引一定要创建在条件列上

（ii）尽量要选择在唯一值多的大表上建立索引

4、Insert

（1）创建测试表

CREATE TABLE `test` (

`id` int(4) NOT NULL,

`name` char(20) NOT NULL,

primary key(id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

（2）指定所有列名插入数据

insert into test(id,name) values(1,'oldboy');

（3）不指定自增列插入数据

insert into test(name) values('oldboy2');

（4）不指定列插入数据：必须对所有列进行插入

a、正确写法

insert into test values(3,'oldboy3');

b、错误写法：没有指定插入自增列

insert into test values('oldboy3');

ERROR 1136 (21S01): Column count doesn't match value count at row 1

（5）批量插入

insert into test values(4,'oldboy4'),(5,'oldboy5'),(6,'oldboy6');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

5、Delete

（1）备份mysest数据库

mysqldump -uroot -p123456 -B mytest > /data/mysql_bak_$(date+%F).sql

（2）删除指定记录

delete from test where id=2;

（3）删除所有记录

delete from test;

6、Select单表查询

（1）查询表中所有信息

select * from test;

（2）查询表中指定列

select name from test;

（3）指定条件查询

select name from test where id=2;

select id,name from test where name='oldboy4';

select id,name from test where name='oldboy4' or id=3;

select id,name from test where id>2 and id<7;

（4）指定返回前3条记录

select name from test limit 3;

select name from test limit 0,3;

（5）指定返回第3到第6条记录

select * from test limit 3,6;

（6）排序

select id,name from test order by id asc;

select id,name from test order by id desc;

7、Select多表查询

（1）创建测试表

a、使用数据库mytest

use mytest

b、创建学生表student(Sno学号,Sname姓名,Ssex性别,Sage年龄,Sdept所在系)

drop tables student;

create table student(

Sno int(10) NOTNULL COMMENT '学号',

Sname varchar(16) NOTNULL COMMENT '姓名',

Ssex char(2) NOTNULL COMMENT '性别',

Sage tinyint(2) NOTNULL default '0'COMMENT '年龄',

Sdept varchar(16) defaultNULL COMMENT '所在系',

PRIMARY KEY (Sno),

key index_Sname (Sname)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULTCHARSET=latin1;

c、创建课程表Course(Cno课程号,Cname课程名,Ccredit学分)

create table course(

Cno int(10) NOTNULL COMMENT '课程号',

Cname varchar(64) NOTNULL COMMENT '课程名',

Ccredit tinyint(2) NOTNULL COMMENT '学分',

PRIMARY KEY (Cno)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULTCHARSET=latin1;

d、创建成绩表SC(Sno学号,Cno课程号,Grade成绩)

CREATE TABLE sc (

SCid int(12)NOT NULL auto_increment COMMENT'主键',

Cno int(10)NOT NULL COMMENT'学号',

Sno int(10)NOT NULL COMMENT'课程号',

Grade tinyint(2)NOT NULL COMMENT'成绩',

PRIMARY KEY(SCid)

) ENGINE=InnoDB DEFAULTCHARSET=latin1;

e、插入模拟数据

INSERT INTO studentvalues(0001,'宏志','男',30,'计算机网络');

INSERT INTO studentvalues(0002,'王硕','男',30,'computerapplication');

INSERT INTO studentvalues(0003,'oldboy','男',28,'物流管理');

INSERT INTO studentvalues(0004,'脉动','男',29,'computerapplication');

INSERT INTO studentvalues(0005,'oldgirl','女',26,'计算机科学与技术');

INSERT INTO studentvalues(0006,'莹莹','女',22,'护士');

INSERT INTO coursevalues(1001,'Linux中高级运维',3);

INSERT INTO c

上一篇： C语言之按位异或

下一篇： .Net加密与解密——散列运算