赞
踩
虚拟账户(对系统来说是假账户)
ftp本身实现:使用文本文件存储账户名和密码
ftp+mysql实现: 利用数据库存储用户名和密码
额外技术:pam插入式验证模块
1、安装软件
安装主程序:
#yum install mysql-server mysql-devel vsftpd pam pam-devel -y
安装pam_mysql模块:
# tar zxvf pam_mysql-0.7RC1.tar.gz
# ./configure --with-openssl && make && make install
2、拷贝pam_mysql模块:
# cp /lib/security/pam_mysql.so /lib64/security/
# service mysqld start
3、在mysql里面创建数据库、表以及在表里面插入用户名和密码。
mysql> create database wuhan;
mysql> use wuhan;
mysql> create table ftp (id int not null primary key auto_increment, name nvarchar(30),
passwd nvarchar(20));
mysql> insert into ftp values ('','user1','123'),('','user2','123'),('','user3','123');
4、授权virtual只能读wuhan数据库的ftp表
mysql> grant select on wuhan.ftp to virtual@localhost identified by '123';
5、建立虚拟用户所需的pam配置文件。
#vim /etc/pam.d/vsftpd.virt
auth required pam_mysql.so user=virtual passwd=123 host=localhost db=wuhan table=ftp usercolumn=name passwdcolumn=passwd crypt=0
account required pam_mysql.so user=virtual passwd=123 host=localhost db=wuhan table=ftp usercolumn=name passwdcolumn=passwd crypt=0
pam_mysql.so //用于linux连接mysql的库文件
user=virtual //用于访问Mysql的用户。
passwd=123 //对应的是用户的密码。
host=localhost //代表mysql在本机。
db=wuhan //指定在mysql上的数据库。
table=ftp //指定mysql上存放用户的表格。
usercolumn=name //指定存储用户名的列。
passwdcolumn=passwd //指定存储密码的列。
crypt=0
crypt=0 //crypt=0: 明文密码
//crypt=1: 使用crpyt()函数(对应SQL数据里的encrypt(),encrypt()随机产生salt)
//crypt=2: 使用MYSQL中的password()函数加密
//crypt=3: 表示使用md5的散列方式
6、配置ftp服务器
#vim /etc/vsftpd/vsftpd.conf
guest_enable=YES
guest_username=virtual
pam_service_name=vsftpd.virt
user_config_dir=/etc/vsftpd/peruser
7、配置用户单独的配置文件
# mkdir /etc/vsftpd/peruser //创建用户单独配置文件的目录
# touch user1 user2 user3
# tail user1 user2 user3
==> user1 <==
local_root=/ftp/user1
==> user2 <==
local_root=/ftp/user2
==> user3 <==
local_root=/ftp/user3
8、创建用户登录目录
# mkdir -p /ftp/user1 /ftp/user2 /ftp/user3
# useradd virtual
# chown -R virtual.virtual /ftp
9、测试:
# lftp -u user1,123 192.168.1.250
ftp本身虚拟账户
=======================
建立存储账户的文件:
# cat /etc/vsftpd/ftpuser.txt
test1 //用户名
123 //密码
test2
123
将存储账户的文本文件转换成pam需要的库文件
# rpm -qf $(which db_load)
db4-utils-4.7.25-17.el6.x86_64
# db_load -T -t hash -f /etc/vsftpd/ftpuser.txt /etc/vsftpd/vsftpd_login.db
配置pam模块
# cat /etc/pam.d/vsftpd.virt
auth required /lib64/security/pam_userdb.so db=/etc/vsftpd/vsftpd_login
account required /lib64/security/pam_userdb.so db=/etc/vsftpd/vsftpd_login
建立所有用户使用的虚拟账户
# useradd virtual
修改vsftpd配置文件
# vim /etc/vsftpd/vsftpd.conf
guest_enable=YES
guest_username=virtual
pam_service_name=vsftpd.virt
user_config_dir=/etc/vsftpd/peruser
配置用户单独的配置文件
# mkdir /etc/vsftpd/peruser //创建用户单独配置文件的目录
# touch test1 test2
# tail test1 test2
==> test1 <==
local_root=/ftp/test1
==> test2 <==
local_root=/ftp/test2
创建用户登录目录
# mkdir -p /ftp/test1 /ftp/test2
# chown -R virtual.virtual /ftp
测试:
# lftp -u test1,123 192.168.1.250
LAMP
Linux+Apache+Mysql+Php
一.rpm
#yum install httpd mysql mysql-server php php-mysql -y
二.编译安装
1.安装apache
下载软件httpd-2.2.25.tar.bz2
#tar xvjf /root/Desktop/httpd-2.2.25.tar.bz2 -C /usr/src/
#./configure --prefix=/usr/local/apache2 --enable-mods-shared=most --enable-so --enable-rewrite --enable-ssl && make && make install
测试apache
#/usr/local/apache2/bin/apachectl start
浏览器: http://172.16.70.50
2.安装mysql
#userdel -r mysql
#useradd mysql
# rm -rf /etc/my.cnf
# rm -rf /var/lib/mysql/
下载软件mysql-5.1.34
安装
#./configure --with-mysqld-user=mysql --prefix=/usr/local/mysql --with-extra-charsets=all --exec-prefix=/usr/local/mysql && make && make install
初始化数据库
产生目录:/usr/local/mysql/var 存放数据库的目录
#cd /usr/loca/mysql/bin
#./mysql_install_db
修改权限
#chown .mysql /usr/loca/mysql/ -R
#chown mysql /usr/local/mysql/var -R
启动数据库:
#./mysqld_safe --user=mysql &
# lsof -i:3306
测试数据库:
#./mysql
3. 安装Php
# ./configure --prefix=/usr/local/php --with-apxs2=/usr/local/apache2/bin/apxs --with-mysql=/usr/local/mysql --with-config-file-path=/usr/local/php && make && make install
# cp php.ini-dist /usr/local/php/php.ini
# /usr/local/php/bin/php -v //查看php版本
4. 测试
apache配置文件:
<IfModule dir_module>
DirectoryIndex index.php index.html //增加index.php
</IfModule>
352 AddType application/x-compress .Z
353 AddType application/x-gzip .gz .tgz
354 AddType application/x-httpd-php .php //增加此行
5.部署网站
安装flash插件
# rpm -ivh flash-plugin-11.2.202.291-release.x86_64.rpm
# unzip farm-ucenter1.5.zip
# cp -r upload/* /usr/local/apache2/htdocs/
# chmod 777 /usr/local/apache2/htdocs/* -R
设置数据库root密码
# /usr/local/mysql/bin/mysqladmin -u root password uplooking
创建数据库
# /usr/local/mysql/bin/mysqladmin -u root -puplooking create discuz
导入农场数据库信息
# /usr/local/mysql/bin/mysql -u root -puplooking -D discuz < qqfarm.sql
安装完以后提示信息:
-----------------------------
UCenter的访问网址:
http://172.16.70.50/ucenter
UCenter 创始人密码:uplooking
DISCUZ!的访问网址:
http://172.16.70.50/bbs
管理员访问网址:
http://172.16.70.50/bbs/admincp.php
管理员帐号:admin 管理员密码:uplooking
UCenter Home的访问网址:
http://172.16.70.50/home
管理员访问网址:
http://172.16.70.50/home/admincp.php
管理员帐号:admin 管理员密码:uplooking
-----------------------------
=================================
mysql
编译安装开机启动
1.把启动命令写到rc.local
2. 使用启动脚本
# pwd
/usr/src/mysql-5.1.34/support-files
#cp mysql.server /etc/init.d/
# chmod 777 /etc/init.d/mysql.server
# chkconfig --add mysql.server
# chkconfig --list | grep mysql
mysql.server 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭
默认情况下Mysql本身有一个root账户,默认没有密码
配置文件位置:
rpm安装方式: /etc/my.cnf
模板文件: /usr/share/doc/mysql-...
编译安装:/etc/my.cnf 安装目录下 var目录下
模板文件:解压目录下的support-files目录
sock文件的位置:mysql.sock
rpm: /var/lib/mysql
编译:/tmp
存储数据库的默认目录
rpm : /var/lib/mysql
编译: var
设置密码:
# mysqladmin -u root password 1
登录数据库:
# mysql -u root -p1
命令结束符:默认是;或者\g
中断当前操作: \c
竖向显示表内容: \G
多实例mysql的安装和管理
mysql的多实例有两种方式可以实现,两种方式各有利弊。
第一种是使用多个配置文件启动不同的进程来实现多实例,这种方式的优势逻辑简单,配置简单,缺点是管理起来不太方便。
第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例,这种方式定制每个实例的配置不太方便,优点是管理起来很方便,集中管理。
下面就分别来实战这两种多实例的安装和管理
第一种:使用多个配置文件启动多个不同进程的情况:
环境介绍:
mysql 版本:5.1.50
mysql实例数:3个
实例占用端口分别为:3306、3307、3308
创建mysql用户
1. /usr/sbin/groupadd mysql
2. /usr/sbin/useradd -g mysql mysql
编译安装mysql
1. tar xzvf mysql-5.1.50.tar.gz
2. cd mysql-5.1.50
3. ./configure '--prefix=/usr/local/mysql' '--with-charset=utf8' '--with-extra-charsets=complex' '--with-pthread' '--enable-thread-safe-client' '--with-ssl' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive' '--enable-shared' '--enable-assembler'
4. make
5. make install
初始化数据库
1. /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3306 --user=mysql
2. /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3307 --user=mysql
3. /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3308 --user=mysql
创建配置文件
vim /data/dbdata_3306/my.cnf
3306的配置文件如下:
1. [client]
2. port = 3306
3. socket = /data/dbdata_3306/mysql.sock
4. [mysqld]
5. datadir=/data/dbdata_3306/
6. skip-name-resolve
7. lower_case_table_names=1
8. innodb_file_per_table=1
9. port = 3306
10. socket = /data/dbdata_3306/mysql.sock
11. back_log = 50
12. max_connections = 300
13. max_connect_errors = 1000
14. table_open_cache = 2048
15. max_allowed_packet = 16M
16. binlog_cache_size = 2M
17. max_heap_table_size = 64M
18. sort_buffer_size = 2M
19. join_buffer_size = 2M
20. thread_cache_size = 64
21. thread_concurrency = 8
22. query_cache_size = 64M
23. query_cache_limit = 2M
24. ft_min_word_len = 4
25. default-storage-engine = innodb
26. thread_stack = 192K
27. transaction_isolation = REPEATABLE-READ
28. tmp_table_size = 64M
29. log-bin=mysql-bin
30. binlog_format=mixed
31. slow_query_log
32. long_query_time = 1
33. server-id = 1
34. key_buffer_size = 8M
35. read_buffer_size = 2M
36. read_rnd_buffer_size = 2M
37. bulk_insert_buffer_size = 64M
38. myisam_sort_buffer_size = 128M
39. myisam_max_sort_file_size = 10G
40. myisam_repair_threads = 1
41. myisam_recover
42. innodb_additional_mem_pool_size = 16M
43. innodb_buffer_pool_size = 200M
44. innodb_data_file_path = ibdata1:10M:autoextend
45. innodb_file_io_threads = 8
46. innodb_thread_concurrency = 16
47. innodb_flush_log_at_trx_commit = 1
48. innodb_log_buffer_size = 16M
49. innodb_log_file_size = 512M
50. innodb_log_files_in_group = 3
51. innodb_max_dirty_pages_pct = 60
52. innodb_lock_wait_timeout = 120
53. [mysqldump]
54. quick
55. max_allowed_packet = 256M
56. [mysql]
57. no-auto-rehash
58. prompt=\\u@\\d \\R:\\m>
59. [myisamchk]
60. key_buffer_size = 512M
61. sort_buffer_size = 512M
62. read_buffer = 8M
63. write_buffer = 8M
64. [mysqlhotcopy]
65. interactive-timeout
66. [mysqld_safe]
67. open-files-limit = 8192
vim /data/dbdata_3307/my.cnf
3307的配置文件如下:
1. [client]
2. port = 3307
3. socket = /data/dbdata_3307/mysql.sock
4. [mysqld]
5. datadir=/data/dbdata_3307/
6. skip-name-resolve
7. lower_case_table_names=1
8. innodb_file_per_table=1
9. port = 3307
10. socket = /data/dbdata_3307/mysql.sock
11. back_log = 50
12. max_connections = 300
13. max_connect_errors = 1000
14. table_open_cache = 2048
15. max_allowed_packet = 16M
16. binlog_cache_size = 2M
17. max_heap_table_size = 64M
18. sort_buffer_size = 2M
19. join_buffer_size = 2M
20. thread_cache_size = 64
21. thread_concurrency = 8
22. query_cache_size = 64M
23. query_cache_limit = 2M
24. ft_min_word_len = 4
25. default-storage-engine = innodb
26. thread_stack = 192K
27. transaction_isolation = REPEATABLE-READ
28. tmp_table_size = 64M
29. log-bin=mysql-bin
30. binlog_format=mixed
31. slow_query_log
32. long_query_time = 1
33. server-id = 1
34. key_buffer_size = 8M
35. read_buffer_size = 2M
36. read_rnd_buffer_size = 2M
37. bulk_insert_buffer_size = 64M
38. myisam_sort_buffer_size = 128M
39. myisam_max_sort_file_size = 10G
40. myisam_repair_threads = 1
41. myisam_recover
42. innodb_additional_mem_pool_size = 16M
43. innodb_buffer_pool_size = 200M
44. innodb_data_file_path = ibdata1:10M:autoextend
45. innodb_file_io_threads = 8
46. innodb_thread_concurrency = 16
47. innodb_flush_log_at_trx_commit = 1
48. innodb_log_buffer_size = 16M
49. innodb_log_file_size = 512M
50. innodb_log_files_in_group = 3
51. innodb_max_dirty_pages_pct = 60
52. innodb_lock_wait_timeout = 120
53. [mysqldump]
54. quick
55. max_allowed_packet = 256M
56. [mysql]
57. no-auto-rehash
58. prompt=\\u@\\d \\R:\\m>
59. [myisamchk]
60. key_buffer_size = 512M
61. sort_buffer_size = 512M
62. read_buffer = 8M
63. write_buffer = 8M
64. [mysqlhotcopy]
65. interactive-timeout
66. [mysqld_safe]
67. open-files-limit = 8192
vim /data/dbdata_3308/my.cnf
3308的配置文件如下:
1. [client]
2. port = 3308
3. socket = /data/dbdata_3308/mysql.sock
4. [mysqld]
5. datadir=/data/dbdata_3308/
6. skip-name-resolve
7. lower_case_table_names=1
8. innodb_file_per_table=1
9. port = 3308
10. socket = /data/dbdata_3308/mysql.sock
11. back_log = 50
12. max_connections = 300
13. max_connect_errors = 1000
14. table_open_cache = 2048
15. max_allowed_packet = 16M
16. binlog_cache_size = 2M
17. max_heap_table_size = 64M
18. sort_buffer_size = 2M
19. join_buffer_size = 2M
20. thread_cache_size = 64
21. thread_concurrency = 8
22. query_cache_size = 64M
23. query_cache_limit = 2M
24. ft_min_word_len = 4
25. default-storage-engine = innodb
26. thread_stack = 192K
27. transaction_isolation = REPEATABLE-READ
28. tmp_table_size = 64M
29. log-bin=mysql-bin
30. binlog_format=mixed
31. slow_query_log
32. long_query_time = 1
33. server-id = 1
34. key_buffer_size = 8M
35. read_buffer_size = 2M
36. read_rnd_buffer_size = 2M
37. bulk_insert_buffer_size = 64M
38. myisam_sort_buffer_size = 128M
39. myisam_max_sort_file_size = 10G
40. myisam_repair_threads = 1
41. myisam_recover
42. innodb_additional_mem_pool_size = 16M
43. innodb_buffer_pool_size = 200M
44. innodb_data_file_path = ibdata1:10M:autoextend
45. innodb_file_io_threads = 8
46. innodb_thread_concurrency = 16
47. innodb_flush_log_at_trx_commit = 1
48. innodb_log_buffer_size = 16M
49. innodb_log_file_size = 512M
50. innodb_log_files_in_group = 3
51. innodb_max_dirty_pages_pct = 60
52. innodb_lock_wait_timeout = 120
53. [mysqldump]
54. quick
55. max_allowed_packet = 256M
56. [mysql]
57. no-auto-rehash
58. prompt=\\u@\\d \\R:\\m>
59. [myisamchk]
60. key_buffer_size = 512M
61. sort_buffer_size = 512M
62. read_buffer = 8M
63. write_buffer = 8M
64. [mysqlhotcopy]
65. interactive-timeout
66. [mysqld_safe]
67. open-files-limit = 8192
创建自动启动文件
vim /data/dbdata_3306/mysqld
3306的启动文件如下:
1. #!/bin/bash
2. mysql_port=3306
3. mysql_username="admin"
4. mysql_password="password"
5.
6. function_start_mysql()
7. {
8. printf "Starting MySQL...\n"
9. /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
10. }
11.
12. function_stop_mysql()
13. {
14. printf "Stoping MySQL...\n"
15. /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
16. }
17.
18. function_restart_mysql()
19. {
20. printf "Restarting MySQL...\n"
21. function_stop_mysql
22. function_start_mysql
23. }
24.
25. function_kill_mysql()
26. {
27. kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
29. }
30.
31. case $1 in
32. start)
33. function_start_mysql;;
34. stop)
35. function_stop_mysql;;
36. kill)
37. function_kill_mysql;;
38. restart)
39. function_stop_mysql
40. function_start_mysql;;
41. *)
42. echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
43. esac
vim /data/dbdata_3307/mysqld
3307的启动文件如下:
1. #!/bin/bash
2. mysql_port=3307
3. mysql_username="admin"
4. mysql_password="password"
5.
6. function_start_mysql()
7. {
8. printf "Starting MySQL...\n"
9. /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
10. }
11.
12. function_stop_mysql()
13. {
14. printf "Stoping MySQL...\n"
15. /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
16. }
17.
18. function_restart_mysql()
19. {
20. printf "Restarting MySQL...\n"
21. function_stop_mysql
22. function_start_mysql
23. }
24.
25. function_kill_mysql()
26. {
27. kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
29. }
30.
31. case $1 in
32. start)
33. function_start_mysql;;
34. stop)
35. function_stop_mysql;;
36. kill)
37. function_kill_mysql;;
38. restart)
39. function_stop_mysql
40. function_start_mysql;;
41. *)
42. echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
43. esac
vim /data/dbdata_3308/mysqld
3308的启动文件如下:
1. #!/bin/bash
2. mysql_port=3308
3. mysql_username="admin"
4. mysql_password="password"
5.
6. function_start_mysql()
7. {
8. printf "Starting MySQL...\n"
9. /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
10. }
11.
12. function_stop_mysql()
13. {
14. printf "Stoping MySQL...\n"
15. /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
16. }
17.
18. function_restart_mysql()
19. {
20. printf "Restarting MySQL...\n"
21. function_stop_mysql
22. function_start_mysql
23. }
24.
25. function_kill_mysql()
26. {
27. kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
29. }
30.
31. case $1 in
32. start)
33. function_start_mysql;;
34. stop)
35. function_stop_mysql;;
36. kill)
37. function_kill_mysql;;
38. restart)
39. function_stop_mysql
40. function_start_mysql;;
41. *)
42. echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
43. esac
启动3306、3307、3308的mysql
1. /data/dbdata_3306/mysqld start
2. /data/dbdata_3307/mysqld start
3. /data/dbdata_3308/mysqld start
更改原来密码(处于安全考虑,还需要删除系统中没有密码的帐号,这里省略了):
1. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock
2. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock
3. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
登录测试并创建关闭mysql的帐号权限,mysqld脚本要用到!
1. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
2. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
3. flush privileges;
4. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
5. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
6. flush privileges;
7. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
8. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
9. flush privileges;
创建了admin帐号以后脚本的stop功能和restart功能就正常了!
更改环境变量
1. vim /etc/profile 添加下面一行内容
2. PATH=${PATH}:/usr/local/mysql/bin/
3. source /etc/profile
添加到自动启动
1. vim /etc/init.d/boot.local
2. /data/dbdata_3306/mysqld start
3. /data/dbdata_3307/mysqld start
4. /data/dbdata_3308/mysqld start
如果是rhel或者centos系统的话自启动文件/etc/rc.local
管理的话,在本地都是采用 -S /data/dbdata_3308/mysql.sock,如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别!
再来看第二种通过官方自带的mysqld_multi来实现多实例实战:
这里的mysql安装以及数据库的初始化和前面的步骤一样,就不再赘述。
mysqld_multi的配置
vim /etc/my.cnf
1. [mysqld_multi]
2. mysqld = /usr/local/mysql/bin/mysqld_safe
3. mysqladmin = /usr/local/mysql/bin/mysqladmin
4. user = admin
5. password = password
6.
7. [mysqld1]
8. socket = /data/dbdata_3306/mysql.sock
9. port = 3306
10. pid-file = /data/dbdata_3306/3306.pid
11. datadir = /data/dbdata_3306
12. user = mysql
13. skip-name-resolve
14. lower_case_table_names=1
15. innodb_file_per_table=1
16. back_log = 50
17. max_connections = 300
18. max_connect_errors = 1000
19. table_open_cache = 2048
20. max_allowed_packet = 16M
21. binlog_cache_size = 2M
22. max_heap_table_size = 64M
23. sort_buffer_size = 2M
24. join_buffer_size = 2M
25. thread_cache_size = 64
26. thread_concurrency = 8
27. query_cache_size = 64M
28. query_cache_limit = 2M
29. ft_min_word_len = 4
30. default-storage-engine = innodb
31. thread_stack = 192K
32. transaction_isolation = REPEATABLE-READ
33. tmp_table_size = 64M
34. log-bin=mysql-bin
35. binlog_format=mixed
36. slow_query_log
37. long_query_time = 1
38. server-id = 1
39. key_buffer_size = 8M
40. read_buffer_size = 2M
41. read_rnd_buffer_size = 2M
42. bulk_insert_buffer_size = 64M
43. myisam_sort_buffer_size = 128M
44. myisam_max_sort_file_size = 10G
45. myisam_repair_threads = 1
46. myisam_recover
47. innodb_additional_mem_pool_size = 16M
48. innodb_buffer_pool_size = 200M
49. innodb_data_file_path = ibdata1:10M:autoextend
50. innodb_file_io_threads = 8
51. innodb_thread_concurrency = 16
52. innodb_flush_log_at_trx_commit = 1
53. innodb_log_buffer_size = 16M
54. innodb_log_file_size = 512M
55. innodb_log_files_in_group = 3
56. innodb_max_dirty_pages_pct = 60
57. innodb_lock_wait_timeout = 120
58.
59.
60. [mysqld2]
61. socket = /data/dbdata_3307/mysql.sock
62. port = 3307
63. pid-file = /data/dbdata_3307/3307.pid
64. datadir = /data/dbdata_3307
65. user = mysql
66. skip-name-resolve
67. lower_case_table_names=1
68. innodb_file_per_table=1
69. back_log = 50
70. max_connections = 300
71. max_connect_errors = 1000
72. table_open_cache = 2048
73. max_allowed_packet = 16M
74. binlog_cache_size = 2M
75. max_heap_table_size = 64M
76. sort_buffer_size = 2M
77. join_buffer_size = 2M
78. thread_cache_size = 64
79. thread_concurrency = 8
80. query_cache_size = 64M
81. query_cache_limit = 2M
82. ft_min_word_len = 4
83. default-storage-engine = innodb
84. thread_stack = 192K
85. transaction_isolation = REPEATABLE-READ
86. tmp_table_size = 64M
87. log-bin=mysql-bin
88. binlog_format=mixed
89. slow_query_log
90. long_query_time = 1
91. server-id = 1
92. key_buffer_size = 8M
93. read_buffer_size = 2M
94. read_rnd_buffer_size = 2M
95. bulk_insert_buffer_size = 64M
96. myisam_sort_buffer_size = 128M
97. myisam_max_sort_file_size = 10G
98. myisam_repair_threads = 1
99. myisam_recover
100. innodb_additional_mem_pool_size = 16M
101. innodb_buffer_pool_size = 200M
102. innodb_data_file_path = ibdata1:10M:autoextend
103. innodb_file_io_threads = 8
104. innodb_thread_concurrency = 16
105. innodb_flush_log_at_trx_commit = 1
106. innodb_log_buffer_size = 16M
107. innodb_log_file_size = 512M
108. innodb_log_files_in_group = 3
109. innodb_max_dirty_pages_pct = 60
110. innodb_lock_wait_timeout = 120
111.
112.
113. [mysqld3]
114. socket = /data/dbdata_3308/mysql.sock
115. port = 3308
116. pid-file = /data/dbdata_3308/3308.pid
117. datadir = /data/dbdata_3308
118. user = mysql
119. skip-name-resolve
120. lower_case_table_names=1
121. innodb_file_per_table=1
122. back_log = 50
123. max_connections = 300
124. max_connect_errors = 1000
125. table_open_cache = 2048
126. max_allowed_packet = 16M
127. binlog_cache_size = 2M
128. max_heap_table_size = 64M
129. sort_buffer_size = 2M
130. join_buffer_size = 2M
131. thread_cache_size = 64
132. thread_concurrency = 8
133. query_cache_size = 64M
134. query_cache_limit = 2M
135. ft_min_word_len = 4
136. default-storage-engine = innodb
137. thread_stack = 192K
138. transaction_isolation = REPEATABLE-READ
139. tmp_table_size = 64M
140. log-bin=mysql-bin
141. binlog_format=mixed
142. slow_query_log
143. long_query_time = 1
144. server-id = 1
145. key_buffer_size = 8M
146. read_buffer_size = 2M
147. read_rnd_buffer_size = 2M
148. bulk_insert_buffer_size = 64M
149. myisam_sort_buffer_size = 128M
150. myisam_max_sort_file_size = 10G
151. myisam_repair_threads = 1
152. myisam_recover
153. innodb_additional_mem_pool_size = 16M
154. innodb_buffer_pool_size = 200M
155. innodb_data_file_path = ibdata1:10M:autoextend
156. innodb_file_io_threads = 8
157. innodb_thread_concurrency = 16
158. innodb_flush_log_at_trx_commit = 1
159. innodb_log_buffer_size = 16M
160. innodb_log_file_size = 512M
161. innodb_log_files_in_group = 3
162. innodb_max_dirty_pages_pct = 60
163. innodb_lock_wait_timeout = 120
164.
165.
166. [mysqldump]
167. quick
168. max_allowed_packet = 256M
169. [mysql]
170. no-auto-rehash
171. prompt=\\u@\\d \\R:\\m>
172. [myisamchk]
173. key_buffer_size = 512M
174. sort_buffer_size = 512M
175. read_buffer = 8M
176. write_buffer = 8M
177. [mysqlhotcopy]
178. interactive-timeout
179. [mysqld_safe]
180. open-files-limit = 8192
mysqld_multi启动
1. /usr/local/mysql/bin/mysqld_multi start 1
2. /usr/local/mysql/bin/mysqld_multi start 2
3. /usr/local/mysql/bin/mysqld_multi start 3
或者采用一条命令的形式:
1. /usr/local/mysql/bin/mysqld_multi start 1-3
更改原来密码(处于安全考虑,还需要删除系统中没有密码的帐号,这里省略了):
1. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock
2. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock
3. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
登录测试并创建admin密码(停止mysql的时候需要使用到)
1. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
2. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
3. flush privileges;
4. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
5. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
6. flush privileges;
7. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
8. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
9. flush privileges;
更改环境变量
1. vim /etc/profile
2. PATH=${PATH}:/usr/local/mysql/bin/
3. source /etc/profile
添加到自动启动
1. vim /etc/init.d/boot.local
2. /usr/local/mysql/bin/mysqld_multi start 1-3
如果是rhel或者centos系统的话自启动文件/etc/rc.local
管理的话,在本地都是采用 -S /data/dbdata_3308/mysql.sock,如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别!
大家在管理多实例的话可以使用mysqlmanager实例管理器,管理器来会比较方面,限于篇幅,这里就不在多做介绍!
本地客户端不用输入密码登录的配置
[client]
user = root
password = 123
=========================================
事务:
在创建表的时候create table xxxx ( ..........) engine innoDB; 后一句表示创建引擎类型为innoDB,它支持事务,
开启一个事务: start transaction;
然后你写你的sql语句,无论你写多少,只要没提交事务,这个事务就存在,有commit显式提交,还有隐式提交,你觉得你写的sql语句没有问题时就,你就commit; 提交这个事务;如果前面你写的sql语句出了问题,比如有条sql语句是批量改金币什么的,改多了。 Rollback;回滚,意思是回到你开启事务时的状态,就是说你开启事务后的所有sql操作当作没有发生,你重新来过。
注意:当一个事务commit,或者rollback就结束了
1005:创建表失败
1006:创建数据库失败
1007:数据库已存在,创建数据库失败
1008:数据库不存在,删除数据库失败
1009:不能删除数据库文件导致删除数据库失败
1010:不能删除数据目录导致删除数据库失败
1011:删除数据库文件失败
1012:不能读取系统表中的记录
1020:记录已被其他用户修改
1021:硬盘剩余空间不足,请加大硬盘可用空间
1022:关键字重复,更改记录失败
1023:关闭时发生错误
1024:读文件错误
1025:更改名字时发生错误
1026:写文件错误
1032:记录不存在
1036:数据表是只读的,不能对它进行修改
1037:系统内存不足,请重启数据库或重启服务器
1038:用于排序的内存不足,请增大排序缓冲区
1040:已到达数据库的最大连接数,请加大数据库可用连接数
1041:系统内存不足
1042:无效的主机名
1043:无效连接
1044:当前用户没有访问数据库的权限
1045:不能连接数据库,用户名或密码错误
1048:字段不能为空
1049:数据库不存在
1050:数据表已存在
1051:数据表不存在
1054:字段不存在
1065:无效的SQL语句,SQL语句为空
1081:不能建立Socket连接
1114:数据表已满,不能容纳任何记录
1116:打开的数据表太多
1129:数据库出现异常,请重启数据库
1130:连接数据库失败,没有连接数据库的权限
1133:数据库用户不存在
1141:当前用户无权访问数据库
1142:当前用户无权访问数据表
1143:当前用户无权访问数据表中的字段
1146:数据表不存在
1147:未定义用户对数据表的访问权限
1149:SQL语句语法错误
1158:网络错误,出现读错误,请检查网络连接状况
1159:网络错误,读超时,请检查网络连接状况
1160:网络错误,出现写错误,请检查网络连接状况
1161:网络错误,写超时,请检查网络连接状况
1062:字段值重复,入库失败
1169:字段值重复,更新记录失败
1177:打开数据表失败
1180:提交事务失败
1181:回滚事务失败
1203:当前用户和数据库建立的连接已到达数据库的最大连接数,请增大可用的数据库连接数或重启数据库
1205:加锁超时
1211:当前用户没有创建用户的权限
1216:外键约束检查失败,更新子表记录失败
1217:外键约束检查失败,删除或修改主表记录失败
1226:当前用户使用的资源已超过所允许的资源,请重启数据库或重启服务器
1227:权限不足,您无权进行此操作
1235:MySQL版本过低,不具有本功能
存储引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)
在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎。
查看引擎:
mysql> show engines;
mysql> SHOW VARIABLES LIKE '%storage_engine%';
mysql> show create table t1;
mysql> show table status like 't1';
关闭不必要的引擎:
不想用哪个,加上跳过哪个就可以,重启mysql,这样也可以优化数据库。
默认如果不想他们启动的话,修改配置文件
#vim /etc/my.cnf
[mysqld]
skip-mrg_myisam
skip-csv
skip-memory
临时指定引擎:
mysql> create table innodb1(id int)engine=innodb;
修改默认引擎:
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
修改已经存在的表的引擎:
mysql> alter table t2 engine=myisam;
MySQL常用存储引擎:
MyISAM存储引擎
由于该存储引擎不支持事务、也不支持外键,所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。
.myi index 存储索引
.myd data 存储数据
.frm 存储表结构
InnoDB存储引擎
由于该存储引擎在事务上具有优势,即支持具有提交、回滚及崩溃恢复能力等事务特性,所以比MyISAM存储引擎占用更多的磁盘空间。
因此当需要频繁的更新、删除操作,同时还对事务的完整性要求较高,需要实现并发控制,建议选择。
大型数据库用innodb
[root@www var]# pwd
/usr/local/mysql/var
[root@www var]# ls ib*
ibdata1 ib_logfile0 ib_logfile1
innodb类型的表的数据存在ibdata1里面,不像myisam生成3个文件, ib_logfile0 ib_logfile1存放日志
初始是10M,每次增加8M ,初始大小可以指定,要修改配置文件
#vim /etc/my.cnf
innodb_data_file_path=ibdata1:20M:autoextend:max:1000M
设定初始大小是20M,增幅也是8M 设定峰值是1000M,就是指定最大能增加到1000M
innodb_data_home_dir=/data
指定他的存储数据的位置,也就是ibdata1的位置
数据分开存储:
比放在一块硬盘里访问速度快
默认的数据都保存在var下面,可以人为的改变他们的存储位置
只适用于innodb类型,.frm文件必须得放在var下,不能指定放到别的地方
#mkdir data 最好是不同的硬盘上
#chown mysql data
mysql> create table tb1(name char(20)) data directory='/data';
data directory=指定数据文件的位置
MEMORY
速度快,比myisam快30%,当数据库重启之后,数据就会丢失,因为他是存在内存里的.适合于需要快速的访问或临时表。
mysql> create table t20(id int,name char(10)) type=memory; 创建一个memory类型的表
BLACKHOLE
黑洞存储引擎,可以应用于主备复制中的分发主库。
mysql> desc blackhole1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into blackhole1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from blackhole1;
Empty set (0.00 sec)
字符集设置
临时:
mysql> create database db1 CHARACTER SET = utf8;
mysql> create table t1(id int(10)) CHARACTER SET = utf8;
永久:
5.1版本设置:
#vim /etc/my.cnf
[mysqld]
default-character-set = utf8
character_set_server = utf8
[mysql]
default-character-set = utf8
5.5版本设置:
[mysqld]
character_set_server = utf8
5.1版本
#vim /etc/my.cnf
[mysqld]
log-slow-queries=/var/lib/mysql/sql_row.log
long_query_time=3
查看是否设置成功:
mysql> show variables like '%query%';
5.5版本
[mysqld]
slow-query-log=on
slow_query_log_file=/var/lib/mysql/slowquery.log
long_query_time=1
#log-queries-not-using-indexes=on //列出没有使用索引的查询语句
5.7版本
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3
# mkdir /var/log/mysql-slow/
# chown mysql.mysql /var/log/mysql-slow/
# systemctl restart mysqld
查看慢查询日志
测试:BENCHMARK(count,expr)
mysql> SELECT BENCHMARK(500000000,2*3);
客户端连接MySQL数据库速度慢的问题
修改my.cnf配置,关闭DNS的反向解析参数
[mysqld]
skip-name-resolve
一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案,暂且抛开前端应用不说,大致分为以下五个阶段:
1、数据库表设计
项目立项后,开发部根据产品部需求开发项目,开发工程师工作其中一部分就是对表结构设计。对于数据库来说,这点很重要,如果设计不当,会直接影响访问速度 和用户体验。影响的因素很多,比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞(死锁)等。当然,有测试工程师的团队,会做压力测试,找 bug。对于没有测试工程师的团队来说,大多数开发工程师初期不会太多考虑数据库设计是否合理,而是尽快完成功能实现和转交付,等项目有一定访问量后,隐 藏的问题就会暴露,这时再去修改就不是这么容易的事了。
2、数据库部署
该运维工程师出场了,项目初期访问量不会很大,所以单台部署足以应对在1500左右的QPS(每秒查询率)。考虑到高可用性,可采用MySQL主从复制+Keepalived做双击热备,常见集群软件有Keepalived、Heartbeat。
双机热备博文:http://lizhenliang.blog.51cto.com/7876557/1362313
3、数据库性能优化
如果将MySQL部署到普通的X86服务器上,在不经过任何优化情况下,MySQL理论值正常可以处理2000左右QPS,经过优化后,有可能 会提升到2500左右QPS,否则,访问量当达到1500左右并发连接时,数据库处理性能就会变慢,而且硬件资源还很富裕,这时就该考虑软件问题了。那么 怎样让数据库最大化发挥性能呢?一方面可以单台运行多个MySQL实例让服务器性能发挥到最大化,另一方面是对数据库进行优化,往往操作系统和数据库默认 配置都比较保守,会对数据库发挥有一定限制,可对这些配置进行适当的调整,尽可能的处理更多连接数。
具体优化有以下三个层面:
3.1 数据库配置优化
MySQL常用有两种存储引擎,一个是MyISAM,不支持事务处理,读性能处理快,表级别锁。另一个是InnoDB,支持事务处理(ACID),设计目标是为处理大容量数据发挥最大化性能,行级别锁。
表锁:开销小,锁定粒度大,发生死锁概率高,相对并发也低。
行锁:开销大,锁定粒度小,发生死锁概率低,相对并发也高。
为什么会出现表锁和行锁呢?主要是为了保证数据的完整性,举个例子,一个用户在操作一张表,其他用户也想操作这张表,那么就要等第一个用户操作完,其他用户才能操作,表锁和行锁就是这个作用。否则多个用户同时操作一张表,肯定会数据产生冲突或者异常。
根据以上看来,使用InnoDB存储引擎是最好的选择,也是MySQL5.5以后版本中默认存储引擎。每个存储引擎相关联参数比较多,以下列出主要影响数据库性能的参数。
公共参数默认值:
点击这里 | 点击这里 |
---|---|
12345678910 | max_connections = 151#同时处理最大连接数,推荐设置最大连接数是上限连接数的80%左右 sort_buffer_size = 2M#查询排序时缓冲区大小,只对order by和group by起作用,可增大此值为16Mquery_cache_limit = 1M #查询缓存限制,只有1M以下查询结果才会被缓存,以免结果数据较大把缓存池覆盖query_cache_size = 16M #查看缓冲区大小,用于缓存SELECT查询结果,下一次有同样SELECT查询将直接从缓存池返回结果,可适当成倍增加此值open_files_limit = 1024 #打开文件数限制,如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时,程序会无法连接数据库或卡死 |
点击这里 | 点击这里 |
---|---|
1234 | key_buffer_size = 16M#索引缓存区大小,一般设置物理内存的30-40%read_buffer_size = 128K #读操作缓冲区大小,推荐设置16M或32M |
点击这里 | 点击这里 |
---|---|
12345678910 | innodb_buffer_pool_size = 128M#索引和数据缓冲区大小,一般设置物理内存的60%-70%innodb_buffer_pool_instances = 1 #缓冲池实例个数,推荐设置4个或8个innodb_flush_log_at_trx_commit = 1 #关键参数,0代表大约每秒写入到日志并同步到磁盘,数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘,I/O开销大,执行完SQL要等待日志读写,效率低。2代表只把日志写入到系统缓存区,再每秒同步到磁盘,效率很高,如果服务器故障,才会丢失事务数据。对数据安全性要求不是很高的推荐设置2,性能高,修改后效果明显。innodb_file_per_table = OFF #默认是共享表空间,共享表空间idbdata文件不断增大,影响一定的I/O性能。推荐开启独立表空间模式,每个表的索引和数据都存在自己独立的表空间中,可以实现单表在不同数据库中移动。innodb_log_buffer_size = 8M #日志缓冲区大小,由于日志最长每秒钟刷新一次,所以一般不用超过16M |
点击这里 | 点击这里 |
---|---|
12345678910 | net.ipv4.tcp_fin_timeout = 30#TIME_WAIT超时时间,默认是60snet.ipv4.tcp_tw_reuse = 1 #1表示开启复用,允许TIME_WAIT socket重新用于新的TCP连接,0表示关闭net.ipv4.tcp_tw_recycle = 1 #1表示开启TIME_WAIT socket快速回收,0表示关闭net.ipv4.tcp_max_tw_buckets = 4096 #系统保持TIME_WAIT socket最大数量,如果超出这个数,系统将随机清除一些TIME_WAIT并打印警告信息net.ipv4.tcp_max_syn_backlog = 4096#进入SYN队列最大长度,加大队列长度可容纳更多的等待连接 |
点击这里 | 点击这里 |
---|---|
1234 | # vi /etc/security/limits.conf #加入以下配置,*代表所有用户,也可以指定用户,重启系统生效* soft nofile 65535* hard nofile 65535# ulimit -SHn 65535 #立刻生效 |
点击这里 | 点击这里 |
---|---|
12 | mysql> show global status like 'Questions'; mysql> show global status like 'Uptime'; |
点击这里 | 点击这里 |
---|---|
123 | mysql> show global status like 'Com_commit'; mysql> show global status like 'Com_rollback'; mysql> show global status like 'Uptime'; |
点击这里 | 点击这里 |
---|---|
1 | mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update'); |
点击这里 | 点击这里 |
---|---|
1 | mysql> show global status where Variable_name in('com_insert','com_delete','com_update'); |
点击这里 | 点击这里 |
---|---|
1234 | mysql> set global slow-query-log=on #开启慢查询功能mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; #指定慢查询日志文件位置mysql> set global log_queries_not_using_indexes=on; #记录没有使用索引的查询mysql> set global long_query_time=1; #只记录处理时间1s以上的慢查询 |
作为MySQL调优的一部分,很多人都推荐开启skip_name_resolve。这个参数是禁止域名解析的(当然,也包括
主机名)。很多童鞋会好奇,这背后的原理是什么,什么情况下开启这个参数比较合适。
基于以下原因,MySQL服务端会在内存中维护着一份host信息, 包括三部分:IP,主机名和错误信息。主要用于
非本地TCP连接。
1. 通过在第一次建立连接时缓存IP和host name的映射关系,同一主机的后续连接将直接查看host cache,而不用再次进行DNS解析。
2. host cache中同样会包含IP登录失败的错误信息。可根据这些信息,对这些IP进行相应的限制。后面将会具体提到。
host cache的信息可通过performance_schema中host_cache表查看。
那么,IP和host name的映射关系是如何建立的呢?
1. 当有一个新的客户端连接进来时,MySQL Server会为这个IP在host cache中建立一个新的记录,包括IP,主机名和client lookup validation flag,分别对应host_cache表中的IP,HOST和HOST_VALIDATED这三列。第一次建立连接因为只有IP,没有主机名,所以 HOST将设置为NULL,HOST_VALIDATED将设置为FALSE。
2. MySQL Server检测HOST_VALIDATED的值,如果为FALSE,它会试图进行DNS解析,如果解析成功,它将更新HOST的值为主机名,并将 HOST_VALIDATED值设为TRUE。如果没有解析成功,判断失败的原因是永久的还是临时的,如果是永久的,则HOST的值依旧为NULL,且将 HOST_VALIDATED的值设置为TRUE,后续连接不再进行解析,如果该原因是临时的,则HOST_VALIDATED依旧为FALSE,后续连 接会再次进行DNS解析。
另,解析成功的标志并不只是通过IP,获取到主机名即可,这只是其中一步,还有一步是通过解析后的主机名来反向解析为IP,判断该IP是否与原IP相同,如果相同,才判断为解析成功,才能更新host cache中的信息。
基于上面的总结,下面谈谈 host cache的优缺点:
缺点:当有一个新的客户端连接进来时,MySQL Server都要建立一个新的记录,如果DNS解析很慢,无疑会影响性能。如果被允许访问的主机很多,也会影响性能,这个与 host_cache_size有关,这个参数是5.6.5引入的。5.6.8之前默认是128,5.6.8之后默认是-1,基于 max_connections的值动态调整。所以如果被允许访问的主机很多,基于LRU算法,先前建立的连接可能会被挤掉,这些主机重新进来时,会再次 进行DNS查询。
优点:通常情况下,主机名是不变的,而IP是多变的。如果一个客户端的IP经常变化,那基于IP的授权将是一个繁琐的过程。因为你很难确定IP什么 时候变化。而基于主机名,只需一次授权。而且,基于host cache中的失败信息,可在一定程度上阻止外界的暴力破解攻击。
关于阻止外界的暴力破解攻击,涉及到max_connect_errors参数,默认为100,官方的解释如下:
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.如果某个客户端的连接达到了max_connect_errors的限制,将被禁止访问,并提示以下错误:
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
下面来模拟一下
首先,设置max_connect_errors的值
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> set global max_connect_errors=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 2 |
+--------------------+-------+
1 row in set (0.00 sec)通过telnet模拟interrupted without a successful connection。
[root@mysql-slave1 ~]# telnet 192.168.244.145 3306
Trying 192.168.244.145...
Connected to 192.168.244.145.
Escape character is '^]'.
N
5.6.26-log
K]qA1nYT!w|+ZhxF1c#|kmysql_native_password
^]
!#08S01Got packets out of orderConnection closed by foreign host.
[root@mysql-slave1 ~]# telnet 192.168.244.145 3306
Trying 192.168.244.145...
Connected to 192.168.244.145.
Escape character is '^]'.
N
Y#>PVB(>!Bl}NKnjIj]sMmysql_native_password
^]
!#08S01Got packets out of orderConnection closed by foreign host.
[root@mysql-slave1 ~]# mysql -h192.168.244.145 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Host '192.168.244.144' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'即便后来使用了正确的账号和密码登录,依旧会被阻止。
再来看看host_cache表中的信息,sum_connect_errors为2了。
mysql> select ip,host,host_validated,sum_connect_errors,count_authentication_errors from performance_schema.host_cache;
+-----------------+------+----------------+--------------------+-----------------------------+
| ip | host | host_validated | sum_connect_errors | count_authentication_errors |
+-----------------+------+----------------+--------------------+-----------------------------+
| 192.168.244.144 | NULL | YES | 2 | 0 |
+-----------------+------+----------------+--------------------+-----------------------------+
1 row in set (0.00 sec)
该阻止会一直生效,直到采取以下操作:
1. mysql> flush hosts;
2. # mysqladmin flush-hosts
3. truncate table performance_schema.host_cache;
4. 或者等待该记录从host cache中被挤掉。
如果要禁止DNS解析,可设置skip_name_resolve参数,这样,mysql.user表中基于主机名的授权将无法使用,且错误日志中会提示:
[Warning] 'user' entry 'root@mysql-slave1' ignored in --skip-name-resolve mode.这里,通过mysql-slave1访问,将会拒绝访问
[root@mysql-slave1 ~]# mysql -h192.168.244.145 -uroot -p123
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.244.144' (using password: YES)
host cache是默认开启的,如果要禁掉,可将host_cache_size设置为0,该参数是个动态参数,可在线修改。
如果要完全禁掉TCP/IP连接,可在MySQL启动时,设置skip-networking参数。
总结:
1. 从原理上看,DNS解析一般只针对客户端的第一次连接,客户端数据量比较小的情况下,开销其实不大,完全不必禁掉skip_name_resolve参数,带来的好处就是,为客户端和多变的IP直接解耦,只需对主机名进行一次授权。
可通过\s查看当前连接使用的是socket还是TCP。
2. 奇怪的是,对于skip_name_resolve参数,虽然官方文档说的是布尔值,
但如果在配置文件中指定了,无论是skip_name_resolve=off或者skip_name_resolve=0。
最后,通过show variables like '%skip_name_resolve%'查看均显示ON。将该参数设置为OFF的唯一办法是不写该参数(因为它默认值即为OFF)。
3. 在skip_name_resolve=ON的情况下,在本地通过-h127.0.0.1没有问题。
[root@localhost ~]# mysql -uroot -h127.0.0.1 -p123456
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.31-log 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.
root@(none) 09:02:15> \s
--------------
mysql Ver 14.14 Distrib 5.6.31, for Linux (x86_64) using EditLine wrapper
Connection id: 4
Current database:
Current user: root@127.0.0.1
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.31-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 11 min 10 sec
Threads: 1 Questions: 20 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.029
--------------
root@(none) 09:02:18> show variables like '%skip_name_resolve%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| skip_name_resolve | ON |
+-------------------+-------+
1 row in set (0.06 sec)
如果该参数设置为OFF,则上述方式就会报错,通过报错信息可以看出,它直接将127.0.0.1转化为localhost了。
[root@localhost ~]# mysql -uroot -h127.0.0.1 -p123456 -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)注意: 't1'@'%'中包含't1'@'127.0.0.1',如果开启skip_name_resolve参数,则't1'@'%'中定义的密码可用于 't1'@'127.0.0.1'的登录,如果没有开启该参数,则't1'@'127.0.0.1'会转化为't1'@'localhost'登录,此时 't1'@'%'定义的密码并不适用。
mysql几种性能测试的工具使用
1、mysqlslap
安装:简单,装了mysql就有了
作用:模拟并发测试数据库性能。
优点:简单,容易使用。
不足:不能指定生成的数据规模,测试过程不清楚针对十万级还是百万级数据做的测试,感觉不太适合做综合测试,比较适合针对既有数据库,对单个sql进行优化的测试。
使用方法:
可以使用mysqlslap --help来显示使用方法:
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
--concurrency代表并发数量,多个可以用逗号隔开,concurrency=10,50,100, 并发连接线程数分别是10、50、100个并发。
--engines代表要测试的引擎,可以有多个,用分隔符隔开。
--iterations代表要运行这些测试多少次。
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的(read,write,update,mixed)
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
--debug-info 代表要额外输出CPU以及内存的相关信息。
--number-int-cols :创建测试表的 int 型字段数量
--auto-generate-sql-add-autoincrement : 代表对生成的表自动添加auto_increment列,从5.1.18版本开始
--number-char-cols 创建测试表的 char 型字段数量。
--create-schema 测试的schema,MySQL中schema也就是database。
--query 使用自定义脚本执行测试,例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--only-print 如果只想打印看看SQL语句是什么,可以用这个选项。
mysqlslap -umysql -p123 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam --number-of-queries=10 --debug-info
或:
指定数据库和sql语句:
mysqlslap -h192.168.3.18 -P4040 --concurrency=100 --iterations=1 --create-schema='test' --query='select * from test;' --number-of-queries=10 --debug-info -umysql -p123
要是看到底做了什么可以加上:--only-print
Benchmark
Average number of seconds to run all queries: 25.225 seconds
Minimum number of seconds to run all queries: 25.225 seconds
Maximum number of seconds to run all queries: 25.225 seconds
Number of clients running queries: 100
Average number of queries per client: 0
以上表明100个客户端同时运行要25秒
2、sysbench
安装:
可以从http://sourceforge.net/projects/sysbench/ 下载
tar zxf sysbench-0.4.12.tar.gz
cd sysbench-0.4.12
./autogen.sh
./configure && make && make install
strip /usr/local/bin/sysbench
安装时候可能会报错,后来baidu发现个好文 http://blog.csdn.net/icelemon1314/article/details/7004955 怕以后找不到,也贴过来吧
1.如果mysql不是默认路径安装,那么需要通过指定--with-mysql-includes和--with-mysql-libs参数来加载mysql安装路径
2.如果报错:
../libtool: line 838: X--tag=CC: command not found
../libtool: line 871: libtool: ignoring unknown tag : command not found
../libtool: line 838: X--mode=link: command not found
../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found
../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found
../libtool: line 2231: X-g: command not found
../libtool: line 2231: X-O2: command not found
那么执行下根目录的:autogen.sh文件,然后重新configure && make && make install
3.如果报错:
sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
那么执行下:
n -s /usr/local/mysql5.5/mysql/lib/libmysqlclient.so.18 /usr/lib64/
4.如果执行autogen.sh时,报如下错误:
./autogen.sh: line 3: aclocal: command not found
那么需要安装一个软件:
yum install automake
然后需要增加一个参数:查找: AC_PROG_LIBTOOL 将其注释,然后增加AC_PROG_RANLIB
作用:模拟并发,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL
优点:可以指定测试数据的规模,可以单独测试读、写的性能,也可以测试读写混合的性能。
不足:测试的时候,由于网络原因,测试的非常慢,但是最终给的结果却很好,并发支持很高,所以给我的感觉是并不太准确。当然也可能我没搞明白原理
使用方法:
准备数据
sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=400000 --mysql-db=dbtest2 --mysql-user=root --mysql-host=192.168.1.101 --mysql-password=pwd prepare
执行测试
sysbench --num-threads=100 --max-requests=4000 --test=oltp --mysql-table-engine=innodb --oltp-table-size=400000 --mysql-db=dbtest1 --mysql-user=root --mysql-host=192.168.1.101 --mysql-password=pwd run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 4000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 56014
write: 20005
other: 8002
total: 84021
transactions: 4001 (259.14 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 76019 (4923.75 per sec.)
other operations: 8002 (518.29 per sec.)
Test execution summary:
total time: 15.4393s
total number of events: 4001
total time taken by event execution: 1504.7744
per-request statistics:
min: 33.45ms
avg: 376.10ms
max: 861.53ms
approx. 95 percentile: 505.65ms
Threads fairness:
events (avg/stddev): 40.0100/0.67
execution time (avg/stddev): 15.0477/0.22
3、tpcc-mysql
安装:
如果从原网站上下载源码比较麻烦,需要工具、注册、生成证书等。这里提供一个下载包http://blog.chinaunix.net/blog/downLoad/fileid/8532.html
export C_INCLUDE_PATH=/usr/include/mysql
export PATH=/usr/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/mysql
cd /tmp/tpcc/src
make
然后就会在 /tmp/tpcc-mysql 下生成 tpcc 命令行工具 tpcc_load 、 tpcc_start
作用:测试mysql数据库的整体性能
优点:符合tpcc标准,有标准的方法,模拟真实的交易活动,结果比较可靠。
不足:不能单独测试读或者写的性能,对于一些以查询为主或者只写的应用,就没有这么大的意义了。
使用方法:
加载数据
创建库
mysql>create database tpcc10;
创建表:
shell>mysql tpcc10 < create_table.sql
添加外键:
shell>mysql tpcc10 < add_fkey_idx.sql
加载数据:
1、单进程加载:
shell>./tpcc_load 192.168.11.172 tpcc10 root pwd 300
|主机||数据库||用户||密码||warehouse|
2、并发加载:(推荐,但需要修改一下)
shell>./load.sh tpcc300 300
|数据库||warehouse|
3、测试
./tpcc_start -h192.168.11.172 -d tpcc -u root -p 'pwd' -w 10 -c 10 -r 10 -l 60 -i 10 -f /mnt/hgfs/mysql/tpcc100_2013522.txt
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '192.168.11.172'
option d with value 'tpcc'
option u with value 'root'
option p with value 'pwd'
option w with value '1'
option c with value '100'
option r with value '120'
option l with value '60'
option i with value '10'
option f with value '/mnt/hgfs/mysql/tpcc100_2013522.txt'
<Parameters>
[server]: 192.168.11.172
[port]: 3306
[DBname]: tpcc
[user]: root
[pass]: pwd
[warehouse]: 1
[connection]: 100
[rampup]: 120 (sec.)
[measure]: 60 (sec.)
RAMP-UP TIME.(120 sec.)
MEASURING START.
10, 245(77):10.923|28.902, 242(0):3.677|10.796, 25(0):1.579|2.198, 24(0):17.451|21.047, 25(4):19.999|33.776
20, 262(75):9.070|11.917, 263(0):3.407|4.716, 26(0):1.608|1.776, 27(0):11.347|16.408, 26(1):19.166|21.018
30, 247(90):11.130|14.131, 241(0):2.367|2.654, 24(0):0.960|1.095, 24(0):9.308|16.538, 25(3):19.999|24.874
40, 237(69):11.840|13.009, 239(1):3.638|7.245, 24(0):0.692|0.773, 23(0):8.756|10.456, 23(1):19.527|20.495
50, 252(69):10.548|17.925, 256(0):2.652|2.893, 26(0):1.177|3.579, 27(0):14.648|15.018, 25(4):19.999|26.398
60, 256(78):9.323|11.328, 251(1):3.895|5.380, 25(0):0.785|1.542, 25(0):11.382|15.829, 26(0):18.481|18.855
STOPPING THREADS....................................................................................................
<Raw Results>
[0] sc:1041 lt:458 rt:0 fl:0
[1] sc:1490 lt:2 rt:0 fl:0
[2] sc:150 lt:0 rt:0 fl:0
[3] sc:150 lt:0 rt:0 fl:0
[4] sc:137 lt:13 rt:0 fl:0
in 60 sec.
<Raw Results2(sum ver.)>
[0] sc:1041 lt:458 rt:0 fl:0
[1] sc:1490 lt:2 rt:0 fl:0
[2] sc:150 lt:0 rt:0 fl:0
[3] sc:150 lt:0 rt:0 fl:0
[4] sc:137 lt:13 rt:0 fl:0
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.36% (>=43.0%) [OK]
Order-Status: 4.36% (>= 4.0%) [OK]
Delivery: 4.36% (>= 4.0%) [OK]
Stock-Level: 4.36% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 69.45% [NG] *
Payment: 99.87% [OK]
Order-Status: 100.00% [OK]
Delivery: 100.00% [OK]
Stock-Level: 91.33% [OK]
<TpmC>
1499.000 TpmC
关于Tpcc的概念请参见http://baike.baidu.com/view/2776305.htm
这里把测试用例介绍贴一下
TPC-C测试用到的模型是一个大型的商品批发销售公司,它拥有若干个分布在不同区域的商品仓库。当业务扩展的时候,公司将添加新的仓库。
每个仓库负责为10个销售点供货,其中每个销售点为3000个客户提供服务,每个客户提交的订单中,平均每个订单有10项产品,
所有订单中约1%的产品在其直接所属的仓库中没有存货,必须由其他区域的仓库来供货。同时,每个仓库都要维护公司销售的100000种商品的库存记录。
4. The MySQL Benchmark Suite
这个测试工具是随着MySQL绑定发行的,基于Perl语言和其中的两个模块:DBI和Benchmark。如果有需要,它支持所有支持DBI驱动的数据库。可以通过修改bench-init.pl的选项以符合需要。另外提醒的是,它不支持多CPU。
进行测试时,执行run-all-tests脚本,具体的命令选项请看README。
5. MySQL super-smack
这是一个强大的广受赞誉的压力测试工具,支持MySQL和PostgreSQL。
http://jeremy.zawodny.com/mysql/super-smack/
安装很简单,请先仔细阅读目录里的指导文件。
Preparing test data
做测试时,最好用自己的数据。因为使用真实的数据,使测试变得接近现实和客观。
Configuration
smack的文件设置,看起来很简单。
6. MyBench: A Home-Grown Solution
MyBench一种基于Perl语言易于扩展的测试工具。
http://jeremy.zawodny.com/mysql/mybench/
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。