mysql多启动与日志调优

最新推荐文章于 2024-10-08 17:39:57 发布

weixin_33763244

最新推荐文章于 2024-10-08 17:39:57 发布

阅读量82

点赞数

文章标签：数据库开发工具 awk

原文链接：http://blog.51cto.com/linuxart/843817

版权

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.socket

mysql> grant shutdown on *.* to 'multi_admin'@'localhost' 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));

在/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> 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/bash

logintty=`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 http://www.google.com/
2008-09-26 23:07:38 192.168.20.171 http://www.google.com/
2008-09-26 23:07:39 192.168.20.171 http://www.google.com/
2008-09-26 23:07:40 192.168.20.171 http://www.google.com/
2008-09-26 23:07:42 192.168.20.171 http://grbc.pw08.iciba.com/dict/dict.php?
2008-09-26 23:07:45 192.168.20.171 http://www.pconline.com.cn/

# 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 '%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   --记录的查询时间
# User@Host: 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 where

mysql> 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> 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 'log'@'localhost' identified by '123';

mysql> flush privileges;

# /usr/local/mysql/bin/mysql -u log -p123 --使用这个用户登录验证就OK了

转载于:https://blog.51cto.com/linuxart/843817