数据库日常管理笔记

applez0718

MySQL何时用

套接字（仅用于unix平台），当命令--host参数的值是localhost时，不可以是127.0.0.1的IP地址的方式。

端口：所有平台

命名管道：用在windows平台上

 

9.1

à       MySQL服务器

mysqld_safe: scripts to start the MySQL daemon and restart it if it dies unexpectedly

mysqld：主程序

à       MySQL客户程序和工具

mysql

mysqladmin：管理性程序

mysqlcheck、isamchk、myisamchk:分析、优化、恢复工具

mysqldump、mysqlhotcopy：备份

 

10.1MySQL数据目录的位置

 

à       mysqladmin -p -u root --host=localhost  [--port= ]  [--socket=  ]variables

注意执行命令的用户home目录中的.my.cnf文件中，不能有#database=sampdb定义

| datadir                         | /var/lib/mysql/

à       mysql> show variables like 'datadir%';

à       ps查看mysqld进程的参数

/usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock

à       cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

 

10.2

InnoDB数据表：把所有数据库里的所有InnoDB数据表全都放在一个公共表空间里

 

10.2.1 MySQL服务器如何提供对数据的访问

除了以下两种情况，mysqld可以独占性的控制数据目录：

à        

10.2.2MySQL数据库在文件系统里如何表示

 

10.2.3 MySQL数据表在文件系统里如何表示

	MyISAM	MERGE	BDB	InnoDB	HEAP	ISAM
.frm-结构定义	*	*	*	*	*	*
.MYD-数据	*
.MYI-索引	*
.MRG		*
.db			*
表空间				*
内存					*
.ISD						*
.ISM						*

db.opt：每一个数据库都有一个
db.opt文件的，都存在于以数据库为名的文件目录下。数据库建立会在数据库目录下的db.opt文件中记录数据库的选项，数据库的字符集设置也
记录在里面。

 

10.2.4 SQL语句如何映射为数据表文件操作

Show tables的输出：与列出某个数据库目录中所有的.frm文件的文件名清单一致；

Creat table的输出：无论何种类型的表，都至少会创建一个.frm文件；

Alter table/creat index/drop index：对.frm文件重新进行编码来反映结构性变化，同时修改数据、索引的内容；

Drop table：对InnoDB、HEAP类型的表，没有对应的文件系统级命令，不可直接删除对应的.frm文件

 

10.2.5操作系统对数据库和数据表命名的限制

大小写问题

10.2.6影响数据表最大尺寸的因素P478

10.2.7数据目录结构对系统性能的影响

缺点：1、导致文件描述符不足

2、打开过多文件，操作系统性能不足（可用ReiserFS文件系统优化，或使用InnoDB数据表）

 

10.2.8MySQL状态文件和日志文件（参考11.4）

常规查询日志

慢查询日志：耗时很长的查询命令的文本。Hostname-slow.log

变更日志：hostname.nnn，推荐使用扩展格式 --log-long-format

二进制变更日志：效率更高的二进制格式写出来的  hostname-bin.nnn

二进制变更日志的索引文件：列出当前正维护着那些“二进制变更日志”hostname-bin.index

错误日志

***：变更日志主要用于db恢复、建立镜像

***：日志记载敏感信息，如口令，需要加强文件安全

 

10.3重新安置数据目录的内容

10.3.1重新安置数据目录的方法P483

à       启动选项

命令行加参数指定 或

在my.cnf文件中定义

à       创建符号链接

需要激活db的符号连接功能，--use-symbolic-links或在my.cnf中添加：use-symbolic-links一行

10.3.2评估重新安置的效果P484

10.3.3重新安置整个数据目录

10.3.4重新安置一个DB

10.3.5重新安置一个数据表（不建议）

10.3.6重新安置InnoDB表空间

10.3.7重新安置状态文件和日志文件

 

第11章

 

 

11.1.1权限表的初始设置情况（mysql.user、mysql.db表的设置）

Root账号：MySQL的超级用户（与系统的root用户完全不是一个）

匿名账号：上述两个表的user一列是空白的是匿名账号；建议都删除

从何处登录： %表示允许从任何地方登录，或某个域中的所有机器”%.domainname”

 

11.1.2为MySQL初始账号设置口令

à       方法一：

Mysqladmin –h localhost –u root password “***”

Mysqladmin –h 此主机的域名 –u root password “***”

上述两命令需要同时使用

à       方法二：

Mysql>set password for 'root'@ 'localhost' = password('***');

Mysql>set password for 'root'@ '此主机的域名' = password('***');

上述两命令需要同时使用

à       方法三：直接修改user权限表的方式，适用所有版本，但需要刷新权限表

Mysql>update user set Password=password('***') where User=""; (阴影部分注意大小写，与表的列名相同)

Mysql>flush privileges;（等同使用mysqladmin -u root reload或mysqladmin -u root flush-privileges）

上述两命令需要同时使用

 

撤销账号超级用户权限

Mysql>revoke all on *.* from '账号'@ 'localhost';

Mysql>revoke grant option on *.* from '账号'@ 'localhost';

 

 

11.2安排MySQL服务器的启动和关闭

11.2.1在unix系统上运行MySQL服务器

1、

à       不要把mysqld运行在root账号下（redhat默认使用mysql账号运行（/etc/init.d/mysqld启动脚本指定使用mysql这个账号和组），可以使用--user参数指定）

à       Mysqld总是运行在同一个账号下

 

2、启动MySQL服务器的方法(redhat推荐使用启动脚本，此脚本使用mysqld_safe脚本来启动mysqld)

à       使用mysqld（不推荐）

à       使用mysqld_safe脚本：会在MySQL服务器启动后，继续监控其运行状况并在其死机时重启；

à       使用启动脚本/etc/init.d/mysqld start|stop|restart

à       使用mysqld_multi

 

11.2.3设定MySQL服务器的启动选项P500

à       在命令行上使用参数

à       在配置文件my.cnf（放在/etc 或数据目录datadir）中指定启动选项（推荐）

程序	使用的my.cnf中选项组
mysqld	[mysqld]、[server]
mysqld_safe	[mysqld]、[server]、[mysqld_safe]
/etc/init.d/mysqld	[mysqld]、[mysqld_server]?应同上
libmysqld(嵌入)	[embedded]、[server]

启动推荐mysqld_safe、和启动脚本方式

11.2.4关闭MySQL服务器P501

à       Mysqladmin –p –u root shutdown

à       /etc/init.d/mysqld stop or service mysqld stop

 

11.2.5当连不上MySQL服务器时，重新获得对MySQL服务器的控制P503

à       口令忘了

解决方法：

l         关闭服务

kill -TERM pid(即kill -15 pid，pid是mysql_safe的)，不可用kill -9

l         重启服务，加参数--skip-grant-tables（This option causes the server not to use the privilege system at all, which gives anyone with access to the server unrestricted access to all databases. You can cause a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload command from a system shell, or by issuing a MySQL FLUSH PRIVILEGES statement after connecting to the server.）

l         设口令

l         重启或重新加载privileges

à       套接字文件被删

解决方法：

可以通过mysqladmin –p –u root –h 127.0.0.1 shutdown（不可使用-h localhost）先关闭服务，再重启，就可以重建套接字文件了;

指定一个更安全的目录放套接字文件

[mysqld]

socket=/path/filename.sock

[client]

socket=/path/filename.sock

 

11.3管理MySQL用户账户

l         Grant：创建账户并设权限

l         Revoke：收回账户权限，但没删除账户

l         直接修改权限表，但需要刷新权限表

Grant、revoke影响的权限表

权限表名称	权限表内容
user	允许连接到MySQL数据库的用户及全局权限
db	数据库级权限
tables_priv	数据表级权限
columns_priv	数据列级权限

***：user表保存 账号、登录的主机、密码、对整个MySQL数据库的权限

     Db表保存  账号、登录的主机、对某个db的权限

11.3.1grant用法P505

Grant 权限 （一个特殊的USAGE权限P512）

On what

To 账户 identified by '密码'

Require （加密连接）

With （转授权限P513或资源限制）

举例：

Grant all

on sampdb.*

to '账号'@ '%|%.域名|192.168.1.%|IP' identified by '密码'

 

11.3.2收回和删除用户

Revoke 权限

On what

From 账号

（经测试：如果此命令收回某个账号对某个db的所有权限，那么在db表中就删除了此账号的记录）

删除账户：

Mysql>delete from user where User= '' and Host= '';

相应删除db、tables_priv、columns_priv这几个权限表里相关的记录；

Mysql>flush privileges;

 

11.3.3修改口令或重设丢失的口令

l         Mysql>set password for ‘账号’@’从哪里访问’ =password(‘***’);

l         Mysql>update user set Password=password(‘***’) where User=’账号’ and Host=’从哪里访问’；

Mysql>flush privileges;

l         Mysql>grant USAGE on 范围 to ‘账号’@’从哪里访问’ identified by ‘***’;

 

11.4维护日志文件P517

常规查询日志(最有用的监控日志)：--log[=host_name.log], -l [host_name.log]

慢查询日志（用mysqldumpslow查看）：耗时很长的查询命令的文本。  --log-slow-queries[=file_name]  Hostname-slow.log

变更日志（逐渐被下面取代）：--log-update[=file_name]  hostname.nnn，推荐使用扩展格式 --log-long-format

二进制变更日志（用mysqlbinlog命令查看）：效率更高的二进制格式写出来的  --log-bin[=file_name不要设文件名后缀]  hostname-bin.nnn

二进制变更日志的索引文件：列出当前正维护着那些“二进制变更日志” --log-bin-index[=file_name，要设文件名后缀index] hostname-bin.index

错误日志（此日志文件需要指定目录（由mysql_safed管理），其他日志都是相对于datadir（由mysqld管理））： --log-error[=file_name]

***：变更日志主要用于db恢复、建立镜像

***：日志记载敏感信息，如口令，需要加强文件安全

 

11.4.6日志文件的失效处理

l         日志轮转

适用范围：对文件名固定不变的日志（例如错误日志），建议采用系统的logrotate

l         以年龄为依据

日志刷新命令：

Mysqladmin –p –uroot flush-logs(不建议使用--refresh参数Flush all tables and close and open logfiles)

Mysql>flush logs;

影响范围：

不包括文件名固定不变的日志（例如错误日志）、BDB/InnoDB的专用日志

l         以镜像机制的进展情况为依据

日志只有被施加到slave服务器上，才可以删除;

连到slave服务器上：mysql>show slave status;来查看（Master_Log_File:…）此slave服务器上正处理着master的哪个日志文件;

连到master服务器上：mysql>purge master logs to ‘日志名’;

 

11.5其他MySQL服务器配置问题

11.5.1对MySQL服务器的连接监听情况进行控制

Mysqld默认端口号：3306

--skip-networking：Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are allowed.将禁止网络连接，但是允许-h localhost（套接字）方式连接

--port：指定端口

--bind-address=IP：绑定指定的IP

 

11.5.2激活、禁用load data语句的local能力

local_infile ：Whether LOCAL is supported for LOAD DATA INFILE statements. See Section 5.4, "Security Issues with LOAD DATA LOCAL".

 

11.5.3

11.5.4选择数据表处理程序(DB是否支持ISAM、BDB、InnoDB等类型的数据表处理程序)

11.5.5配置InnoDB表空间

à       组成：*.frm和表空间（类似一个虚拟文件系统）

à       至少有两个选项来设置（建议加在my.cnf文件中，或者默认生成的配置如下）：

innodb_data_file_path    ibdata1:10M:autoextend （只有一个表空间，名称ibdata1，大小10M（最小），自动扩展，默认放在mysql的datadir（/var/lib/mysql/），非某个db的子目录中；多个表空间用;分隔）

innodb_data_home_dir                         （）

à       如何创建：

通过在my.cnf文件中添加上述两条设置，或者不设置，直接创建InnoDB类型的表，可自动生成

à       如何把未格式化的原始硬盘用做InnoDB表空间：P533

innodb_data_file_path=/dev/rdsk[/ibdata1]:2Gnewraw

innodb_data_home_dir=

关停mysqld

innodb_data_file_path=/dev/rdsk[/ibdata1]:2Graw

innodb_data_home_dir=

启动mysqld

à       日志(默认自动创建，切放在datadir目录（/var/lib/mysql/）)：

Ib_为前缀，如：ib_logfile[0]

à       表空间不足

mysql> show table status like 'ibdata1%';查看剩余空间

l         关停mysqld

前提：最后一个表空间是可扩展的，把最后一个表空间大小固定(1M=1048567字节)，添加新的表空间到此行innodb_data_file_path=ibdata1:122M; ibdata2:62M; ibdata3:10M: autoextend

启动mysqld

l         通过备份，再恢复的方式

 

11.5.6优化MySQL服务器

1.设置和查看MySQL服务器变量

命令行加参数或者在/etc/my.cnf中设置（变量中用连字符“-”或下划线“_”都可，通过show variables;命令查看到的变量用的是下划线“_”）

两项改进：

l         很多可在系统运行时设置，但只影响到mysqld关停

l         作用范围分----全局级和会话级

Set global 变量 = value;

Set @@global.变量 = value;

以下是会话级

Set local变量 = value;

Set @@local.变量 = value;

Set session变量 = value;

Set @@session.变量 = value;

Set变量 = value;

Set @@变量 = value;

建立之初用全局级设置，建立之后，全局级的设置不影响会话级的变量

l         查看变量

mysql> show variables;查看所有变量

mysql> show global variables;

mysql> show local variables;= mysql> show session variables;

2.一些对性能影响很大的变量P538

。。。

。。。

文件描述符不足的解决：

 

 

 

mysqld_multi --no-log start 1,2

 

实验：在同一机器启动多个mysqld，并实现完全镜像

l         先完成master、slave之间的最初同步（备份、copy等）

l         关闭master、slave：mysqld_multi --no-log stop 1,2或mysqladmin -p -uroot -h 127.0.0.1 --port 40002 shutdown

l         [root@centos5 mysql2]# cat /etc/my.cnf

[mysqld1]

#####master##########

server-id=1

log-bin=/var/lib/mysql1/1-bin

####################

basedir=/var/lib/mysql1

datadir=/var/lib/mysql1

mysqld=/usr/bin/mysqld_safe1

socket=/var/lib/mysql1/mysql.sock

port=40001

user=mysql

group=mysql

log=/var/lib/mysql1/log1

log-error=/var/lib/mysql1/mysqlerr1.log

log-bin-index=/var/lib/mysql1/1-bin.index

innodb-data-file-path=ibdata1:10M:extended

language=/usr/share/mysql/english

 

[mysqld2]

#####slave##########

server-id=2

master-host=127.0.0.1

master-port=40001

master-user=slave_user

master-password=slave_pass

####################

basedir=/var/lib/mysql2

datadir=/var/lib/mysql2

mysqld=/usr/bin/mysqld_safe2

socket=/var/lib/mysql2/mysql.sock

port=40002

user=mysql

group=mysql

log=/var/lib/mysql2/log2

log-error=/var/lib/mysql2/mysqlerr2.log

log-bin=/var/lib/mysql2/2-bin

log-bin-index=/var/lib/mysql2/2-bin.index

innodb-data-file-path=ibdata2:10M:extended

language=/usr/share/mysql/english

 

[mysqld_mutli]

mysqld=/usr/bin/mysqld_safe

mysqladmin=/usr/bin/mysqladmin

user=root

password=juyou2011

l         重启master（重启前备份现有的二进制日志）

mysqld_multi --no-log start 1

在master上创建一个用于镜像的账号

mysql> grant replication slave on *.* to 'slave_user'@'127.0.0.1' identified by 'slave_pass';

mysql> reset master;（将清除之前所有的二进制日志，创建一个最新的filename.00001）

l         重启slave

自动创建master.info文件，保存与master的连接参数和在镜像工作状态。以后再重启，优先读取此文件，非配置文件，所以如果修改了my.cnf，必须先删除master.info。

l         如何监控、管理master/slave

à       Master

mysql> purge master;删除master上不会再被slave用到的二进制日志(前提条件，指向此master的所有slave都执行了mysql> show slave status;命令后，才可以使用此命令)

à       Slave

mysql> slave stop;挂起（可以通过show slave status的输出部分发现，Slave_IO_Running: Yes  Slave_SQL_Running: Yes两行停止）

mysql> slave start;恢复

mysql> show slave status;查看镜像协调信息，确定master上哪些二进制文件失效

*************************** 1. row slave***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 127.0.0.1

                Master_User: slave_user

                Master_Port: 40001

              Connect_Retry: 60

            Master_Log_File: 1-bin.000001

        Read_Master_Log_Pos: 303

             Relay_Log_File: centos5-relay-bin.000006

              Relay_Log_Pos: 436

      Relay_Master_Log_File: 1-bin.000001

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB:

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 0

                 Last_Error:

               Skip_Counter: 0

        Exec_Master_Log_Pos: 303

            Relay_Log_Space: 436

            Until_Condition: None

             Until_Log_File:

              Until_Log_Pos: 0

         Master_SSL_Allowed: No

         Master_SSL_CA_File:

         Master_SSL_CA_Path:

            Master_SSL_Cert:

          Master_SSL_Cipher:

             Master_SSL_Key:

      Seconds_Behind_Master: 0

1 row in set (0.00 sec)

 

mysql> show processlist\G

*************************** 1. row master***************************

     Id: 4

   User: root

   Host: localhost:52291

     db: sampdb

Command: Query

   Time: 0

  State: NULL

   Info: show processlist

*************************** 2. row ***************************

     Id: 6

   User: slave_user

   Host: localhost:34149

     db: NULL

Command: Binlog Dump

   Time: 465

  State: Has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

2 rows in set (0.01 sec)

 

mysql> CHANGE MASTER TO

    MASTER_HOST = 'host_name'

  | MASTER_USER = 'user_name'

  | MASTER_PASSWORD = 'password'

  | MASTER_PORT = port_num

  | MASTER_CONNECT_RETRY = interval

  | MASTER_LOG_FILE = 'master_log_name'

  | MASTER_LOG_POS = master_log_pos

  | RELAY_LOG_FILE = 'relay_log_name'

  | RELAY_LOG_POS = relay_log_pos

  | MASTER_SSL = {0|1}

  | MASTER_SSL_CA = 'ca_file_name'

  | MASTER_SSL_CAPATH = 'ca_directory_name'

  | MASTER_SSL_CERT = 'cert_file_name'

  | MASTER_SSL_KEY = 'key_file_name'

  | MASTER_SSL_CIPHER = 'cipher_list'

修改slave上的几个镜像参数，需要先执行slave stop,然后修改，然后再slave start

à       可在master、slave都能使用的命令，查看复制情况

mysql> show processlist\G

l         镜像部分db

在my.cnf的master部分添加：binlog-ignore-db=dbname(那么对此db的修改，就不会记载到二进制变更日志里了)

l