當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql多启动与日志调优

發(fā)布時(shí)間：2025/5/22 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql多启动与日志调优小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

mysql 多啟動(dòng)

mysqld_multi 可以管理多個(gè)幀聽不同Unix套接字文件和TCP/IP端口的連接的mysqld? 進(jìn)程。它可以啟動(dòng)或停止服務(wù)器，或報(bào)告它們的當(dāng)前狀態(tài)。

rpm 版默認(rèn)也支持，源碼版需要在編譯時(shí)加上

第一步：手動(dòng)編寫這個(gè)多啟動(dòng)配置文件
# vim /usr/local/mysql/etc/mysqld_multi.cnf

[mysqld_multi]??--多啟動(dòng)的參數(shù)組，不要寫錯(cuò)
mysqld=/usr/local/mysql/bin/mysqld_safe??--啟動(dòng)時(shí)使用的程序
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

第二步：
創(chuàng)建目錄，并修改其權(quán)限
# 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

第三步：
初始化要管理的多個(gè)數(shù)據(jù)庫

# /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啟動(dòng)多個(gè)數(shù)據(jù)庫

# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2

也可以使用下面的命令來啟動(dòng)；--config-file參數(shù)將會(huì)被--defaults-extra-file參數(shù)替代
[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???

報(bào)告數(shù)據(jù)庫狀態(tài)用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

分別使用這多個(gè)數(shù)據(jù)的方法：
就是使用mysql連接時(shí)，指定不同的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

第五步：
授權(quán)關(guān)閉數(shù)據(jù)的權(quán)限
# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,1,2

--使用上面命令stop后發(fā)現(xiàn)并不能關(guān)閉這多個(gè)數(shù)據(jù)庫，因?yàn)槊總€(gè)數(shù)據(jù)庫還需要對multi_admin用戶進(jìn)行授權(quán)

連接第一個(gè)數(shù)據(jù)庫授權(quán)
# /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;

--每個(gè)數(shù)據(jù)庫都要重復(fù)做一次上面的操作，都要授權(quán)

都授權(quán)后，可以很靈活的關(guān)閉和開啟指定的數(shù)據(jù)庫
# /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

-------------------------------------------------------------

上面的程序是沒有服務(wù)腳本的，要支持chkconfig，就得手動(dòng)寫

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實(shí)例管理器(IM)是通過TCP/IP端口運(yùn)行的后臺程序，用來監(jiān)視和管理MySQL數(shù)據(jù)庫服務(wù)器實(shí)例。MySQL實(shí)例管理器適合Unix-類操作系統(tǒng)和Windows。

rpm 版默認(rèn)也支持，源碼版需要在編譯時(shí)加上--withmysqlmanager?

# /usr/local/mysql/libexec/mysqlmanager ?--啟動(dòng)程序

第一步：
手動(dòng)編寫配置文件
# 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??--監(jiān)控時(shí)間間隔
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??--這一句不加啟動(dòng)會(huì)報(bào)錯(cuò)

[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

第二步：
創(chuàng)建相關(guān)目錄，修改權(quán)限
# 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

第三步：
初始化數(shù)據(jù)庫
# /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

第四步：創(chuàng)建密碼文件
# /usr/local/mysql/libexec/mysqlmanager --passwd >> /usr/local/mysql/etc/my.passwd

# cat /usr/local/mysql/etc/my.passwd
li:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

--因?yàn)閙ysql5.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:

第五步：啟動(dòng)數(shù)據(jù)庫
# /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;

================================================================

從一個(gè)外部表把數(shù)據(jù)裝載到數(shù)據(jù)庫的表內(nèi)??--oracle里有SQL_LOADER這種工具?

兩個(gè)方法：
mysql> load data local infile ....???? --編譯時(shí)要加上--enable-local-infile參數(shù)

# mysqlimport??
它就是一個(gè)load data local infile的一個(gè)功能的打包實(shí)現(xiàn)

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下建立一個(gè)文件emp.txt，內(nèi)容如下，都是用制表符（使用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
小強(qiáng)??? m?????? 2008-08-06????? 4500
小泉工中一狼??? m?????? 2009-01-03????? 3000

導(dǎo)入方法
第一種：
/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表給導(dǎo)入到數(shù)據(jù)庫

--注意：此功能安全性方面不太強(qiáng)，有些情況下需要禁用它

例：我只有一個(gè)phpwind論壇的用戶，卻可以把系統(tǒng)里的敏感信息如/etc/passwd給裝載到數(shù)據(jù)庫內(nèi)來查看
[root@li ~]# /usr/local/mysql/bin/mysql -u phpwind -p123

mysql> use phpwind??--phpwind僅對此庫是有所有權(quán)限的?

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給裝到數(shù)據(jù)庫進(jìn)行查看

\n 代表linux系統(tǒng)回車鍵的行結(jié)束符號

windows默認(rèn)為\r\n

可以使用下面的命令互相轉(zhuǎn)換
dos2unix
unix2dos

練習(xí)：
如何使用mysqlimport導(dǎo)/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

============================================================

實(shí)現(xiàn)用戶登錄，自動(dòng)執(zhí)行腳本，把登錄的用戶名，登錄時(shí)間等信息插入到數(shù)據(jù)庫

規(guī)劃一個(gè)表，用于存放相關(guān)信息
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??? 日志整理后插入到數(shù)據(jù)庫

date?? time???? IP?????? 網(wǎng)址

# 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

===============================================================

慢查詢?nèi)罩?/p>

用來記錄執(zhí)行時(shí)間比較長的語句，方便DBA定位數(shù)據(jù)庫性能問題

mysql> show variables like '%long%';
+-----------------+-----------+
| Variable_name?? | Value???? |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

--下面我設(shè)置參數(shù)把慢查詢?nèi)罩竟δ艽蜷_，并且設(shè)置查詢時(shí)間大小1秒的就記錄
mysql> set global slow_query_log=on;

mysql> set long_query_time=1;

--上面的設(shè)置是馬上生效，但重啟不生效。所以要重啟也生效，把參數(shù)加到配置文件里

# vim /usr/local/mysql/etc/my.cnf
[mysqld]

log_slow_queries=/var/log/mysqld/mysql5-slowquery.log?--打開慢查詢?nèi)罩竟δ?#xff0c;并定義路徑
long_query_time=1?--定義查詢時(shí)間大于1秒的就記錄到慢查詢?nèi)罩?#xff0c;小于1秒的不記錄，默認(rèn)為10秒，可以使用show variables like '%long%';查看

--重啟服務(wù)
[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?? --只記錄查詢時(shí)間大于1秒的查詢
# Time: 110801 16:02:12 ??--記錄的查詢時(shí)間
# User@Host: root[root] @ localhost []?--記錄的查詢用戶
# Query_time: 5.603300? Lock_time: 0.000090 Rows_sent: 2466836? Rows_examined: 2466836??--記錄查詢所消耗時(shí)間，鎖定時(shí)間，操作的行數(shù)
use ccc;
SET timestamp=1312185732;?--設(shè)置timestamp，是1970年1月1號0點(diǎn)到現(xiàn)在的秒數(shù)
select * from squid_log;?--記錄的語句

-------------------------------------------------

索引

加快查詢速度(select)，注意索引根據(jù)源表數(shù)據(jù)改變，所以反而會(huì)減慢DML語句的速度

???btree? 二叉樹

建立索引的基本原則：
對于經(jīng)常查詢，少做DML操作的表
對于經(jīng)常用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查看一條語句的執(zhí)行計(jì)劃
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)?--花的時(shí)間非常短

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??--走了一個(gè)叫PRIMARY的索引，因?yàn)槲以诮ū頃r(shí)id列用的是primary_key主鍵約束，默認(rèn)會(huì)創(chuàng)建索引
????????? 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)? --建完索引，再次查詢上面那條語句，時(shí)間非常短了

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

--物理上去驗(yàn)證大小，看到.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

驗(yàn)證插入速度的減慢
因?yàn)樵趗rl列上加了一條索引，把這張表truncate掉。然后再導(dǎo)入200多W行，會(huì)發(fā)現(xiàn)速度慢很多

==========================================================

-----------------------------------------------------

view?? 視圖

視圖是虛擬存在的表

數(shù)據(jù)獨(dú)立性
安全性
簡單性

mysql> create view squid_logview? as select ip,url from squid_log;

mysql> show tables;?--是可以查看得到的

--出于安全性問題，把視圖授權(quán)給一個(gè)用戶能夠查看
mysql> grant select on source.squid_logview to 'log'@'localhost' identified by '123';

mysql> flush privileges;

# /usr/local/mysql/bin/mysql -u log -p123?--使用這個(gè)用戶登錄驗(yàn)證就OK了

轉(zhuǎn)載于:https://blog.51cto.com/linuxart/843817

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的mysql多启动与日志调优的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

日志
MySQL

上一篇：处理一键安装LNMP环境之后，phpin
下一篇：创建表分区的总结