mysql 多启动
mysqld_multi 可以管理多个帧听不同Unix套接字文件和TCP/IP端口的连接的mysqld 进程。它可以启动或停止服务器，或报告它们的当前状态。
rpm 版默认也支持，源码版需要在编译时加上
第一步：手动编写这个多启动配置文件 # vim /usr/local/mysql/etc/mysqld_multi.cnf
[mysqld_multi] --多启动的参数组，不要写错
mysqld=/usr/local/mysql/bin/mysqld_safe --启动时使用的程序 mysqladmin=/usr/local/mysql/bin/mysqladmin --管理程序 user=multi_admin --定义管理用户名 password=multipass --管理用户的密码 [mysqld00] port=3300 datadir=/data00 pid-file=/var/run/mysqld/mysql00.pid socket=/var/run/mysqld/mysql00.socket general_log general_log_file=/var/log/mysqld/mysql00.log log-error=/var/log/mysqld/mysql00-err.log [mysqld01] port=3301 datadir=/data01 pid-file=/var/run/mysqld/mysql01.pid socket=/var/run/mysqld/mysql01.socket general_log general_log_file=/var/log/mysqld/mysql01.log log-error=/var/log/mysqld/mysql01-err.log [mysqld02] port=3302 datadir=/data02 pid-file=/var/run/mysqld/mysql02.pid socket=/var/run/mysqld/mysql02.socket general_log general_log_file=/var/log/mysqld/mysql02.log log-error=/var/log/mysqld/mysql02-err.log
第二步：
创建目录，并修改其权限 # mkdir /data00 /data01 /data02 /var/run/mysqld /var/log/mysqld# chown mysql.mysql /data00 /data01 /data02 /var/run/mysqld /var/log/mysqld /usr/local/mysql/etc/mysqld_multi.cnf
第三步： 初始化要管理的多个数据库# /usr/local/mysql/bin/mysql_install_db --datadir=/data00/ --user=mysql
# /usr/local/mysql/bin/mysql_install_db --datadir=/data01/ --user=mysql
# /usr/local/mysql/bin/mysql_install_db --datadir=/data02/ --user=mysql
第四步，用mysqld_multi启动多个数据库# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2
也可以使用下面的命令来启动；--config-file参数将会被--defaults-extra-file参数替代 [root@li ~]# /usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2
# netstat -ntlup |grep 33
tcp 0 0 0.0.0.0:3300 0.0.0.0:* LISTEN 3855/mysqld tcp 0 0 0.0.0.0:3301 0.0.0.0:* LISTEN 3494/mysqld tcp 0 0 0.0.0.0:3302 0.0.0.0:* LISTEN 3507/mysqld报告数据库状态用report
# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf report 0,1,2 Reporting MySQL servers MySQL server from group: mysqld00 is running MySQL server from group: mysqld01 is running MySQL server from group: mysqld02 is running 分别使用这多个数据的方法： 就是使用mysql连接时，指定不同的socket# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql00.socket
# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql01.socket
# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql02.socket
第五步：
授权关闭数据的权限 # /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,1,2 --使用上面命令stop后发现并不能关闭这多个数据库，因为每个数据库还需要对multi_admin用户进行授权
连接第一个数据库授权
# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql00.socketmysql> grant shutdown on *.* to identified by 'multipass';
mysql> flush privileges;
--每个数据库都要重复做一次上面的操作，都要授权都授权后，可以很灵活的关闭和开启指定的数据库
# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,2 # /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf report 0,1,2 Reporting MySQL servers MySQL server from group: mysqld00 is not running MySQL server from group: mysqld01 is running MySQL server from group: mysqld02 is not running -------------------------------------------------------------上面的程序是没有服务脚本的，要支持chkconfig，就得手动写
vim /etc/init.d/mysqld_multi
#!/bin/bash # chkconfig: - 86 16 # description: start and stop the mysqld_multi service.start () {
/usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2 }stop () {
/usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,1,2 }case "$1" in
start ) start sleep 2 echo " ...........................[OK]" ;; stop ) stop sleep 2 echo " ...........................[OK]" ;; restart ) stop sleep 5 start echo ".........................[OK]" ;; * ) echo "USAGE:start|stop|restart" ;; esac # chmod 755 /etc/init.d/mysqld_multi
=============================================================
--------------------------------------------------------------
sandbox 沙盒
MySQL实例管理器(IM)是通过TCP/IP端口运行的后台程序，用来监视和管理MySQL数据库服务器实例。MySQL实例管理器 适合Unix-类操作系统和Windows。
rpm 版默认也支持，源码版需要在编译时加上--withmysqlmanager
# /usr/local/mysql/libexec/mysqlmanager --启动程序第一步：
手动编写配置文件 # vim /usr/local/mysql/etc/mysqlmanager.cnf[manager]
default-mysqld-path=/usr/local/mysql/libexec/mysqld socket=/var/run/mysqld/manager.socket pid-file=/var/run/mysqld/manager.pid password-file=/usr/local/mysql/etc/my.passwd --定义密码文件 monitoring-interval=2 --监控时间间隔 port=1999 --管理端口 bind-address=2.2.2.10 --管理IP [mysqld03] port=3303 datadir=/data03 socket=/var/run/mysqld/mysql03.socket pid-file=/var/run/mysqld/mysql03.pid general_log general_log_file=/var/log/mysqld/mysql03.log log-err=/var/log/mysqld/mysql03-err.log user=mysql --这一句不加启动会报错[mysqld04]
port=3304 datadir=/data04 socket=/var/run/mysqld/mysql04.socket pid-file=/var/run/mysqld/mysql04.pid general_log general_log_file=/var/log/mysqld/mysql04.log log-err=/var/log/mysqld/mysql04-err.log user=mysql[mysqld05]
port=3305 datadir=/data05 socket=/var/run/mysqld/mysql05.socket pid-file=/var/run/mysqld/mysql05.pid general_log general_log_file=/var/log/mysqld/mysql05.log log-err=/var/log/mysqld/mysql05-err.log user=mysql 第二步： 创建相关目录，修改权限 # mkdir /data03 /data04 /data05 /var/log/mysqld /var/run/mysqld/# chown mysql.mysql /data03 /data04 /data05 /var/log/mysqld /var/run/mysqld/ /usr/local/mysql/etc/mysqlmanager.cnf
第三步： 初始化数据库 # /usr/local/mysql/bin/mysql_install_db --datadir=/data03 --user=mysql# /usr/local/mysql/bin/mysql_install_db --datadir=/data04 --user=mysql
# /usr/local/mysql/bin/mysql_install_db --datadir=/data05 --user=mysql
第四步：创建密码文件
# /usr/local/mysql/libexec/mysqlmanager --passwd >> /usr/local/mysql/etc/my.passwd# cat /usr/local/mysql/etc/my.passwd
li:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257 --因为mysql5.1.x版本上面的mysqlmanager程序有BUG，不能生成，所以就用rpm版mysqlmanager程序来替代生成 # /usr/libexec/mysqlmanager --passwd > /usr/local/mysql/etc/my.passwd Creating record for new user. Enter user name: li Enter password: Re-type password: 第五步：启动数据库 # /usr/local/mysql/libexec/mysqlmanager --defaults-file=/usr/local/mysql/etc/mysqlmanager.cnf &第六步：通过管理IP地址登录
# /usr/local/mysql/bin/mysql -h 2.2.2.10 -P 1999 -u li -p123 mysql> show instances; mysql> show instance status mysqld03; mysql> stop instance mysqld03; mysql> start instance mysqld03; mysql> show instance options mysqld03; mysql> show mysqld1 log files;
================================================================
从一个外部表把数据装载到数据库的表内 --oracle里有SQL_LOADER这种工具
两个方法： mysql> load data local infile .... --编译时要加上--enable-local-infile参数 # mysqlimport 它就是一个load data local infile的一个功能的打包实现mysql> create table emp (ename varchar(10),sex char(1),hiredate date,sal int(4));
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | int(4) | YES | | NULL | | 在/tmp下建立一个文件emp.txt，内容如下，都是用制表符（使用tab键）隔开 zhangsan m 2005-10-03 5000 lisi m 2004-04-03 6000 wangwu f 2003-06-04 8000 maliu m 2006-06-06 5500 小强 m 2008-08-06 4500 小泉工中一狼 m 2009-01-03 3000
导入方法
第一种： /usr/local/mysql/bin/mysqlimport source /tmp/emp.txt -p123 --文件和表名保持一致
第二种：
mysql> use source; mysql> load data local infile '/tmp/emp.txt' into table emp;例：把/etc/passwd表给导入到数据库
--注意：此功能安全性方面不太强，有些情况下需要禁用它
例：我只有一个phpwind论坛的用户，却可以把系统里的敏感信息如/etc/passwd给装载到数据库内来查看
[root@li ~]# /usr/local/mysql/bin/mysql -u phpwind -p123 mysql> use phpwind --phpwind仅对此库是有所有权限的mysql> create table password (
-> username varchar(30), -> password char(1), -> uid int(5), -> gid int(5), -> comment varchar(50), -> homedir varchar(30), -> shell varchar(20));mysql> desc password;
+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(30) | YES | | NULL | | | password | char(1) | YES | | NULL | | | uid | int(5) | YES | | NULL | | | gid | int(5) | YES | | NULL | | | comment | varchar(50) | YES | | NULL | | | homedir | varchar(30) | YES | | NULL | | | shell | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ mysql> load data local infile '/etc/passwd' into table password fields terminated by ':' lines terminated by '\n' ; --可以把/etc/passwd给装到数据库进行查看 \n 代表linux系统回车键的行结束符号windows默认为\r\n
可以使用下面的命令互相转换
dos2unix unix2dos
练习：
如何使用mysqlimport导/etc/passwd到mysql中的ccc.password表
[root@li ~]# cp /etc/passwd /etc/password
[root@li ~]# /usr/local/mysql/bin/mysqlimport -p123 phpwind --fields-terminated-by=":" --lines-terminated-by="\n" /etc/password phpwind.password: Records: 66 Deleted: 0 Skipped: 0 Warnings: 0
============================================================
实现用户登录，自动执行脚本，把登录的用户名，登录时间等信息插入到数据库 规划一个表，用于存放相关信息 mysql> create table login ( -> id int not null auto_increment primary key, -> user varchar(30), -> date date, -> time time, -> source_ip varchar(20));not null 非空约束
auto_increment 自增列 primary key 主键
vim /tmp/login.sh
#!/bin/bashlogintty=`ps |grep bash |awk '{print $2}'`
username=`whoami` source_ip=`who |grep $logintty |awk '{print $NF}'` /usr/local/mysql/bin/mysql -u root -p123 << EOF insert into source.login(user,date,time,source_ip) values ("$username",current_date(),current_time(),"$source_ip") EOF 写完脚本后 在/etc/profile最后写上一句 sh /tmp/login.sh------------------------------------------------------------------ 把/share/soft/access.log 日志整理后插入到数据库
date time IP 网址
# head /share/soft/access.log |awk ' {if ($7 ~ "http://") print strftime ("%Y-%m-%d %H:%M:%S",$1),$3,$7}'
2008-09-26 23:07:36 192.168.20.171 2008-09-26 23:07:38 192.168.20.171 2008-09-26 23:07:39 192.168.20.171 2008-09-26 23:07:40 192.168.20.171 2008-09-26 23:07:42 192.168.20.171 ? 2008-09-26 23:07:45 192.168.20.171 # cat /share/soft/access.log |awk ' {if ($7 ~ "http://") print strftime ("%Y-%m-%d %H:%M:%S",$1),$3,$7}' > /access.txt
mysql> create table squid_log (
-> id int not null auto_increment primary key, -> date date, -> time time, -> ip varchar(20), -> url varchar(1000));mysql> desc squid_log;
+-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | date | YES | | NULL | | | time | time | YES | | NULL | | | ip | varchar(20) | YES | | NULL | | | url | varchar(1000) | YES | | NULL | | +-------+---------------+------+-----+---------+----------------+ mysql> load data local infile '/access.txt' into table squid_log fields terminated by ' ' lines terminated by '\n'(date,time,ip,url); Query OK, 2466836 rows affected, 15 warnings (35.15 sec) Records: 2466836 Deleted: 0 Skipped: 0 Warnings: 15
===============================================================
慢查询日志用来记录执行时间比较长的语句，方便DBA定位数据库性能问题
mysql> show variables like '%slow%';
+---------------------+---------------------------------+ | Variable_name | Value | +---------------------+---------------------------------+ | log_slow_queries | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/run/mysqld/mysql5-slow.log | +---------------------+---------------------------------+mysql> show variables like '%long%';
+-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ --下面我设置参数把慢查询日志功能打开，并且设置查询时间大小1秒的就记录 mysql> set global slow_query_log=on;mysql> set long_query_time=1;
--上面的设置是马上生效，但重启不生效。所以要重启也生效，把参数加到配置文件里
# vim /usr/local/mysql/etc/my.cnf
[mysqld]log_slow_queries=/var/log/mysqld/mysql5-slowquery.log --打开慢查询日志功能，并定义路径
long_query_time=1 --定义查询时间大于1秒的就记录到慢查询日志，小于1秒的不记录，默认为10秒，可以使用show variables like '%long%';查看 --重启服务 [root@li ~]# /usr/local/mysql/bin/mysqladmin shutdown -p123 [root@li ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &mysql> select * from squid_log; .............. 2466836 rows in set (5.60 sec) [root@li ~]# cat /var/log/mysqld/mysql5-slowquery.log --只记录查询时间大于1秒的查询 # Time: 110801 16:02:12 --记录的查询时间 # : root[root] @ localhost [] --记录的查询用户 # Query_time: 5.603300 Lock_time: 0.000090 Rows_sent: 2466836 Rows_examined: 2466836 --记录查询所消耗时间，锁定时间，操作的行数 use ccc; SET timestamp=1312185732; --设置timestamp，是1970年1月1号0点到现在的秒数 select * from squid_log; --记录的语句 -------------------------------------------------
索引
加快查询速度(select)，注意索引根据源表数据改变，所以反而会减慢DML语句的速度
btree 二叉树
建立索引的基本原则：
对于经常查询，少做DML操作的表 对于经常用where限制条件的列
mysql> select * from squid_log where url='http://www.itpub.net/medalimgs/y7.gif';
22 rows in set (1.38 sec) --花了1.38秒--用explain查看一条语句的执行计划
mysql> explain select * from squid_log where url='http://www.itpub.net/medalimgs/y7.gif'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: squid_log type: ALL possible_keys: NULL --没有走索引 key: NULL key_len: NULL ref: NULL rows: 2466836 --扫描了2466836行 Extra: Using wheremysql> select * from squid_log where id='244236'; 1 row in set (0.00 sec) --花的时间非常短 mysql> explain select * from squid_log where id='244236'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: squid_log type: const possible_keys: PRIMARY --走了一个叫PRIMARY的索引，因为我在建表时id列用的是primary_key主键约束，默认会创建索引 key: PRIMARY key_len: 4 ref: const rows: 1 --直接通过索引去取行，所以这里为1 Extra: --通过数据库的信息表可以查询到这个索引 mysql> select table_name,column_name,index_name,index_type from information_schema.STATISTICS where table_name='squid_log'; +------------+-------------+------------+------------+ | table_name | column_name | index_name | index_type | +------------+-------------+------------+------------+ | squid_log | id | PRIMARY | BTREE | +------------+-------------+------------+------------+ 1 row in set (0.00 sec) mysql> create index idx_squid_log_url on squid_log(url); Query OK, 2466836 rows affected, 2 warnings (11 min 4.37 sec) Records: 2466836 Duplicates: 0 Warnings: 0
mysql> select * from squid_log where url='http://www.itpub.net/medalimgs/y7.gif'; 22 rows in set (0.00 sec) --建完索引，再次查询上面那条语句，时间非常短了 mysql> explain select * from squid_log where url='http://www.itpub.net/medalimgs/y7.gif'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: squid_log type: ref possible_keys: idx_squid_log_url --走了索引 key: idx_squid_log_url key_len: 1003 ref: const rows: 3 Extra: Using where --物理上去验证大小，看到.MYI索引文件也很大了 -rw-rw---- 1 mysql mysql 8.5K Aug 1 16:31 squid_log.frm -rw-rw---- 1 mysql mysql 198M Aug 1 16:42 squid_log.MYD -rw-rw---- 1 mysql mysql 61M Aug 1 16:42 squid_log.MYI
验证插入速度的减慢
因为在url列上加了一条索引，把这张表truncate掉。然后再导入200多W行，会发现速度慢很多========================================================== -----------------------------------------------------
view 视图
视图是虚拟存在的表
数据独立性
安全性 简单性 mysql> create view squid_logview as select ip,url from squid_log;mysql> show tables; --是可以查看得到的
--出于安全性问题，把视图授权给一个用户能够查看
mysql> grant select on source.squid_logview to identified by '123'; mysql> flush privileges; # /usr/local/mysql/bin/mysql -u log -p123 --使用这个用户登录验证就OK了