tar zxf cmake-2.8.8.tar.gz
cd cmake-2.8.8
./configure
gmake
gmake install
cd ..
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
tar xf mysql-5.5.32.tar.gz
cd mysql-5.5.32
useradd mysql -s /sbin/nologin -M
id mysql
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 \
make && make install
ln -s /application/mysql-5.5.32/ /application/mysql
echo 'export PATH=/application/mysql/bin/:$PATH' >>/etc/profile
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
ll -d /application/mysql/data/
drwxr-xr-x 3 root root 4096 Mar 26 22:10 /application/mysql/data/
chown -R mysql.mysql /application/mysql/data/
chmod 1777 /tmp/
cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf
cd /application/mysql/scripts/
./mysql_install_db --user=mysql --basedir=/application/mysql/--datadir=/application/mysql/data/
cp/application/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
/etc/init.d/mysqld start
/application/mysql/bin/mysqladmin -u rootpassword '123456'
mysql -uroot -p
delete from mysql.user where host='::1'or user='';
drop database test;
grant all privileges on *.* tosystem@'localhost' identified by '123456' with grant option
chkconfig mysqld on
chkconfig --list mysqld
mysqld 0:off 1:off2:on 3:on 4:on5:on 6:off
就是一台服务器上开启多个不同的服务端口,来运行多个MySQL服务里程,通过不用同socket 监听不同的服务端口提供各自的服务。
这些MySQL多实例,共用一套MySQL安装程序,使用不同(也可相同)的my.conf配置文件、启动程序、数据文件。在提供服务时,多实例的MySQL在逻辑上看起来是各自独立的,多个实例根据配置文件对应的设定值,获取相应的服务器资源。
a、有效利用服务器资源
b、节约服务器资源
c、节省IDC机柜
a、资源互相抢占
b、当某个服务实例并发很高或有慢查询时导致其它实例性能下降
a、资金紧张的公司
b、并发访问不是特别大的业务
c、门户网站应用较多
百度搜索引擎的数据库是多实例,一般是从库(48核,96G,3-4个实例)
sina网也是多实例(内存48G)
tree data
/data
├── 3306
│ ├── data #3306实例的数据文件
│ ├── my.cnf #3306实例的配置文件
│ └── mysql #3306实例的启动文件
└── 3307
├── data
├── my.cnf
└── mysql
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、存在的问题
耦合性太强
yum install ncurses-devel -y
yum install libaio-devel -y
整个安装过程只到make install和建立软链接就停止,后面的步骤不用操作,多实例方式需重新配置。
pkill mysqld
ps -ef | grep mysql
rm -f /etc/init.d/mysqld
mkdir -p /data/{3306,3307}/data
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
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
当启动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
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
fi
}
#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
fi
}
#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
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
fi
}
#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
fi
}
#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
chown -R mysql.mysql /data
find /data -type f -name mysql | xargs chmod +x
echo 'export PATH=/application/mysql/bin/:$PATH' >>/etc/profile
#命令在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
#命令在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
mysql_install_db --user=mysql
/data/3306/mysql start
/data/3307/mysql start
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
ps -ef | grep mysql
mysqladmin -u root -S /data/3306/mysql.sock password '123456'
mysqladmin -u root -S /data/3307/mysql.sock password '123456'
(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
mysql -S /data/3306/mysql.sock
create database d3306;
mysql -S /data/3307/mysql.sock
create database d3307;
mysql -S /data/3306/mysql.sock
system mysql -S /data/3307/mysql.sock
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| d3307 |
| mysql |
| performance_schema |
| test |
在关闭MySQL多实例数据库时,是需要密码的,所以在/data/{port}/mysql中是要写入MySQL用户密码的。一定要与真实密码相匹配,否则无法关闭数据库。同时,由于密码放在启动脚本中,存在安全问题,所以要把启动脚本文件的权限改为700.
grep mysql_pwd /data/3306/mysql
mysql_pwd="123456"
/data/3306/mysql stop
/data/3307/mysql stop
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 {} \;
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
/data/3306/mysql stop
/data/3307/mysql stop
/data/3306/mysql start
/data/3307/mysql start
本地连接MySQL数据库,需要指定sock文件,远程连接时,只需指定IP和端口
mysql -uroot -p123456 -h 192.168.1.3 -P 3307
mkdir -p /data/3308/data
cp /data/3306/my.cnf /data/3308/
cp /data/3306/mysql /data/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
server-id = 5
chown -R mysql.mysql 3308/
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql --datadir=/data/3308/data--user=mysql
/data/3308/mysql start
mysqladmin -u root -S /data/3308/mysql.sock password '123456'
mysql -S /data/3308/mysql.sock -uroot -p
create database d3308;
/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 &
/etc/init.d/mysqld start
ss -lnt | grep 3306
ps -ef | grep mysql | grep -v grep
mysqld_safe --user=mysql
a、当需找回root密码时,经常用mysql_safe --user=mysql &带参数启动。
b、自己载发脚本启动MySQL数据库时,可能会使用到这个启动方法
c、/etc/init.d/mysqld和mysql_safe --user=mysql &的启动实质是一样的。
a、mysqladmin方法
mysqladmin -uroot -p123456 shutdown
b、自带脚本
/etc/init.d/mysqld stop
c、kill信号的方法(最好不用)
kill -USR2 `cat path/pid`
killall mysqld
pkill mysqld
killall -9 mysqld
a、用killall关闭,要直到出现mysqld: no process killed才表示完成关闭操作
b、一般在生产环境尽量不要用强制关闭数据库的方法,在生产环境中,高并发业务时,可能会引起数据丢失。甚至导致数据库无法启动的故障。
野蛮粗鲁杀死数据库导致故障企业案例见备注参考博文19
mysql -uroot -p
a、本地登陆
mysql -S /data/3308/mysql.sock -uroot -p
b、远程登陆
mysql -uroot -p -h 192.168.1.3 -P3306
cat /root/.mysql_history
mysql>
mysql> prompt \u@mysql \r:\m:\s>
PROMPT set to '\u@mysql \r:\m:\s>'
root@mysql 02:18:11>
[mysql] #不是[mysqld]
prompt=\\u@mysql \\r:\\m:\\s>
所有操作在登陆MySQL后进行。
mysql> help
mysql> help show
mysql> help grant
Bye
quit
exit
ctrl + c
ctrl + d
grant all privileges on *.* to system@'localhost' identified by '123456'with grant option;
delete from mysql.user where user<>'system'
a、单实例
mysqladmin -u root password '123456';
b、多实例
mysqladmin -u root -S /data/3307/mysql.sock password '123456'
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;
/etc/init.d/mysqld stop
mysqld_safe -–skip-grant-tables -–user=mysql &
mysql
或
mysql –uroot –p #直接回车,无密码
a、不能用mysqladmin方式修改密码,因为mysqladmin在用户存在密码时修改密码,是要求提供原密码的。
b、只能使用SQL语句来修改
update mysql.user set password=password("456") whereuser='root' and host='localhost';
flush privileges;
quit;
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
mysql –uroot –p456
killall mysqld #只能用killall了,尽量不要用kill -9
mysqld_safe –-defaults-file=/data/3306/my.cnf -–skip-grant-tables -–user=mysql&
说明:-–defaults-file必须在-–skip-grant-tables
mysql –S /data/3306/mysql.sock
update mysql.user set password=password("456") whereuser='root' and host='localhost';
flush privileges;
quit;
killall mysqld
ps –ef | grep mysql
/data/3306/mysql start
mysql –uroot –p456 –S /data/3306/mysql.sock
create database
不能以数字开头
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
use
use test1;
select database();
a、如果没有使用use dbname,则显示为NULL
select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
b、使用use dbname,则显示相应的dbname
use test1
Database changed
select database();
+------------+
| database() |
+------------+
| test1 |
+------------+
drop database
drop database test1;
select version();
+-----------+
| version() |
+-----------+
| 5.1.72 |
+------------+
select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
select user();
+---------------------+
| now() |
+---------------------+
| 2017-03-27 21:15:46 |
+---------------------+
show tables;
Empty set (0.00 sec) #新建的库,没有表
show tables from test2;
show tables in test2;
drop user "user"@"主机域"
可以是单引号,也可以是双引号。但是不能不加。如果主机名中有大写字母的则drop删除不了,可以用delete语句来删除mysql.user表中的用户。
drop user 'root'@'localhost';
drop user 'system'@"localhost";
drop user ''@"localhost";
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;
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';
a、语法
GRANT ALL PRIVILEGES ON dbname.* to 'username'@'主机域' identified by 'password';
b、说明
GRANT |
ALL PRIVILEGES |
ON dbname.* |
to 'username'@'主机域' |
identified by 'password' |
授权命令 |
对应权限 |
目标:库和表 |
用户名和客户端主机 |
用户密码 |
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;
flush privileges;
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 |
+--------------------+
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
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
对于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'@'%'
show variables;
show global status;
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
show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host |db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | NULL| Query | 0 | NULL| show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
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
create database mytest;
show create database mytest\G;
*************************** 1. row ***************************
Database: mytest
Create Database: CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTERSET latin1 */
use mytest;
a、通过帮助获取
mysql> help create table
b、常用格式
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <表名>(
<字段1><类型1>,
<字段2><类型2>,
...
<字段n><类型n>
);
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;
a、方法一:desc
desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null |Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null |Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO| | NULL ||
| name | char(20) | NO| | NULL ||
| age | tinyint(2) | NO| | 0 ||
| dept | varchar(16) |YES || NULL | |
+-------+-------------+------+-----+---------+-------+
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;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null |Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO| PRI | NULL | auto_increment |
| name | char(20) | NO| MUL | NULL | |
| 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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null |Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO| PRI | NULL | |
| name | char(20) | NO| MUL | NULL | |
| age | tinyint(2) | NO| | 0 ||
| dept | varchar(16) |YES || NULL | |
+-------+-------------+------+-----+---------+-------+
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
(ii)示例
alter table student3 add index myIndex_student3_name(name);
alter table student3 add index myIndex_student3_dept(dept);
a、删除主键索引
alter table student2 drop primary key;
b、删除普通索引
alter table student3 drop index myIndex_student3_name;
or
drop index myIndex_student3_name on student;
a、语法(Syntax)
create index index_name on table_name(column_name(length));
b、示例
create index myStudentIndexName on student(dept(8));
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等是无法走索引的。
a、语法(Syntax)
create unique index index_name on table_name(column_name);
b、示例
create unique index myStudentUnionIndexName on student(age);
show index from student\G
a、问一:既然索引可以加快查询速度,那么就给所有的列建索引吧?
解答:
索引会加快查询速度,但会影响更新速度。索引不仅占用系统空间,更新数据库时还需维护索引数据。因此,索引是一把双刃剑,并不是越多越好,例如:数十到几百行的小表上无需建索引,写频繁、读少的业务要少建索引。
b、到底在哪些列上创建索引?
解答:
(i)索引一定要创建在条件列上
(ii)尽量要选择在唯一值多的大表上建立索引
CREATE TABLE `test` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into test(id,name) values(1,'oldboy');
insert into test(name) values('oldboy2');
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
insert into test values(4,'oldboy4'),(5,'oldboy5'),(6,'oldboy6');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysqldump -uroot -p123456 -B mytest > /data/mysql_bak_$(date+%F).sql
delete from test where id=2;
delete from test;
select * from test;
select name from test;
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;
select name from test limit 3;
select name from test limit 0,3;
select * from test limit 3,6;
select id,name from test order by id asc;
select id,name from test order by id desc;
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
如对本文有疑问, 点击进行留言回复!!
MySQL Innodb 存储结构 和 存储Null值 用法详解
网友评论