windos mysql启动日志位置 mysql启动失败日志

转载
mob64ca13fd559d 2024-05-31 09:53:44
文章标签 windos mysql启动日志位置数据库开发工具 awk mysql 文章分类 MySQL 数据库
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 '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));
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 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 '%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   --记录的查询时间
 # 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> 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 'log'@'localhost' identified by '123'; mysql> flush privileges;
 # /usr/local/mysql/bin/mysql -u log -p123 --使用这个用户登录验证就OK了
https://blog.51cto.com/linuxart/843817
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。