mysql日常管理维护-2.doc

mysql日常管理维护1 mysql安装mysql unix下安装有三种安装方式：rpm安装，二进制安装及源码安装安装步骤（二进制方式）1.1创建mysql用户组rootmysql script# groupadd mysql1.2 创建mysql用户rootmysql script# useradd -g mysql mysql1.3执行安装（mysql-VERSION-OS.tar.gz安装包已经上传至mysql用户家目录）rootmysql # cd /home/mysqlrootmysql mysql# tar -xzvf /home/mysql/mysql-VERSION-OS.tar.gzrootmysql mysql# ln -s mysql-VERSION-OS.tar.gz mysqlrootmysql mysql# cd mysql rootmysql mysql# scripts/mysql_install_db -user=mysql1.4 修改目录权限rootmysql # cd /home/mysql/mysqlrootmysql #chown -R root:mysql .rootmysql #chown -R mysql:mysql datarootmysql #bin/mysqld_safe -user=mysql &2 mysql数据目录2.1数据目录位置查询数据目录位置，有两种方法方法一：rootmysql mysql# mysqladmin -uroot -proot123 variables|grep datadir| datadir方法二：mysql show variables like datadir;+-+-+| Variable_name | Value |+-+-+| datadir | /var/lib/mysql/ |+-+-+2.2数据目录结构1每个数据库在数据目录下一个数据库目录2每个数据库的表，视图，触发器等在数据库自己的目录下有相应文件3服务器进程文件。mysql启动时候时将进程写入进程文件，停止后删除进程文件，默认进程文件位置在数据目录下，文件名为hostname.PID4mysql服务器生成状态文件及日志文件5与mysql服务器相关的文件3 数据库管理3.1创建数据库rootmysql mysql# mysql -uroot -proot123mysql create database db_name;create database db_name会在数据目录下生成一个db_name子目录，同时在这个数据库子目录db_name下生成一个db.opt文件，里面列出了数据库的字符类型及排序方式。注：数据目录查询mysql show variables like datadir;+-+-+| Variable_name | Value |+-+-+| datadir | /var/lib/mysql/ |+-+-+3.2删除数据库mysql drop database db_name;如果不能使用这个命令进行删除，需要将这个子目录下多余的文件进行手工删除，然后进行drop database 命令继续删除。3.3查看已有数据库mysql show databases；3.4 表 视图 触发器的文件表现方式3.4.1表的文件表现方式创建表后会有生成三个文件：tablename.frm：格式文件，描述表结构tablename.MYD：数据文件，存储表数据tablename.MYI：索引文件，存储表的索引信息3.4.2视图的文件表现方式viewname.frm:视图定义及相关属性3.4.3触发器的文件表现方式Targer.TRG:触发器定义及相关属性4 用户及权限4.1用户创建mysql create user iman identified by iman123; mysql select user from mysql.user where user=iman;+-+| user |+-+| iman |+-+4.2 用户删除命令删除：mysqldrop user username;删除表数据：mysqldelete from mysql.user where host= “ ”and user=” ”;4.3密码修改4.3.1 root密码修改1使用mysqladmrootmysql mysql# mysqladmin -uroot -p password newpassword2使用update user表：mysqlupdate mysql.user set password=password(“newpassword”) where user=” ” and host=”;3set 修改使用root用户登录mysqlmysql set password=password=(“newpassword”);4.3.2普通用户密码修改mysql set password=password=(“newpassword”);4.3.3 root密码丢失找回1rootmysql mysql# mysqld_safe -skip-grant-tables user=mysql2rootmysql mysql#mysql -u root 3mysqlupdate mysql.user set password=password=(“newpassword”) where user=rootAnd host=localhost;4mysqlflush privileges;4.4用户权限赋权限：mysql grant all on *.* to iman; 收回权限：mysql revoke all on *.* from iman;4.5 用户权限查看mysql show grants;mysqlshow grants for “username”;5 表的操作维护创建表，删除表，更新表，插入表数据都和其他数据的的一样，主要介绍下5.1检查修复表5.1.1用服务器1检查mysql check table ap_portlet;+-+-+-+-+| Table | Op | Msg_type | Msg_text |+-+-+-+-+| ITWORKS.ap_portlet | check | status | OK |+-+-+-+-+2修复mysql repair table ap_portlet;+-+-+-+-+| Table | Op | Msg_type | Msg_text |+-+-+-+-+| ITWORKS.ap_portlet | repair | status | OK |+-+-+-+-+5.1.2 mysqlcheckmysqlcheck一步可以完成检查修复单表rootmysql mysql# mysqlcheck -uroot -proot123 ITWORKS ap_portlet;ITWORKS.ap_portlet OK 制定库所有表rootmysql mysql# mysqlcheck -uroot -proot123 ITWORKSITWORKS.ap_portlet OKrootmysql mysql# mysqlcheck -uroot -proot123 ITWORKSITWORKS.AGENT OKITWORKS.AGENT_CFG_CHANGE_AND_REQUEST OKITWORKS.AGENT_COLLECT_TABLE_INFO OKITWORKS.AGENT_CONFIG_METADATA OKITWORKS.AGENT_HISTORY_STATUS OKITWORKS.AGENT_MONITOR_PARAMETER OKITWORKS.ALERT_CASEFILTER OK所有库所有表rootmysql mysql# mysqlcheck -uroot -proot123 -all-database5.1.3 myisamchkMyisamchk用来检查修复MyISAM表1检查rootmysql mysql# myisamchk /var/lib/mysql/mysql/plugin.MYI Checking MyISAM file: /var/lib/mysql/mysql/plugin.MYIData records: 1 Deleted blocks: 0- check file-size- check record delete-chain- check key delete-chain- check index reference- check data record references index: 1- check record links2修复rootmysql mysql# myisamchk -recover /var/lib/mysql/mysql/help_keyword.MYD myisamchk: error: /var/lib/mysql/mysql/help_keyword.MYD is not a MyISAM-tablerootmysql mysql# myisamchk -recover /var/lib/mysql/mysql/help_keyword.MYI - recovering (with sort) MyISAM-table /var/lib/mysql/mysql/help_keyword.MYIData records: 450- Fixing index 1- Fixing index 26 日志文件mysql的日志有二进制日志文件，错误日志，通用查询日志和慢查询日志。这些日志可以有助于了解数据库运行状况，日常操作，错误信息和哪些地方需要优化。6.1二进制日志文件6.1.1打开二进制日志文件通常二进制日志文件是关闭的，通过增加/etc/f文件的log-bin选项打开二进制日志文件。形式如下：log-bin = ./masterlog这样就会产生二进制日志文件，生成的文件格式为masterlog.number,比如masterlog.0000011编辑/etc/f文件rootmysql mysql# vi /etc/f2查看修改结果rootmysql mysql# cat /etc/f |grep log-binlog-bin = ./masterlog3重启mysqlservice mysqld restart 4查看生成的二进制文件rootmysql mysql# mysql -uroot -proot123mysql show master logs;+-+-+| Log_name | File_size |+-+-+| masterlog.000016 | 107 | masterlog.000017 | 126 | masterlog.000018 | 126 | masterlog.000019 | 126 | masterlog.000020 | 126 | masterlog.000021 | 519 | masterlog.000022 | 126 | masterlog.000023 | 126 | masterlog.000024 | 107 | masterlog.000025 | 107 |+-+-+10 rows in set (0.00 sec)mysql 注：具体的二进制日志文件生成位置mysql show variables like datadir;+-+-+| Variable_name | Value |+-+-+| datadir | /var/lib/mysql/ |+-+-+1 row in set (0.00 sec)这样生成的二进制文件就在/var/lib/mysql/ 目录下6.1.2分析二进制日志文件1查看二进制日志文件rootmysql mysql# mysqlbinlog masterlog.0000162日志文件保存为文本rootmysql mysql# mysqlbinlog masterlog.000016textfile3提取特定数据语句rootmysql mysql# mysqlbinlog -database=dbname masterlog.000016textfile 4提取从某个时间段的日志rootmysql mysql# mysqlbinlog -start-datetime=20130919000000 masterlog.000016.1.3删除二进制日志文件1删除所有mysql reset master;2根据编号删除mysql purge master logs to logfilename;3根据创建时间删除mysql purge master logs to 2013-09-19 00:00:00;6.1.4暂时停止二进制日志文件1暂停mysql set sql_log_bin=0;2恢复mysql set sql_log_bin=1;6.2错误日志6.2.1打开错误日志错误日志默认是开启的，参数log-error为错误日志参数，没有此参数会在会在datadir路径下生成hostname.err错误日志，修改错误日志文件路径和日志文件名，添加log-error在/etc/f文件就行。1格式log-error=DIR/filename6.2.2删除错误日志rootmysql mysql# mysqladmin -uroot -proot123 flush-logs 执行这个命令后，会产生新的错误日志，旧的错误会更名为filename.err-old6.3通用查询日志通用查询日志是记录用户的所有操作，包括启动和关闭mysql服务，更新和查询语句等。6.3.1打开日志通用查询日志默认是关闭的，参数log为错误日志参数，没有此参数会在会在datadir路径下，通用查询日志文件路径和日志文件名，添加log在/etc/f文件就行。1格式log=DIR/filename6.3.2删除错误日志rootmysql mysql# mysqladmin -uroot -proot123 flush-logs 6.4慢查询日志慢查询日志是用来记录执行语句超过制定时间的查询语句，用来分析性能，调整性能的。6.3.1打开日志通用查询日志默认是关闭的，参数log-slow-queries和long_query_time为慢查询日志参数，修改慢查询日志文件路径和日志文件名，添加log在/etc/f文件就行。1格式log-slow-queries=DIR/filenamelong_query_time=n5.3.2删除慢查询日志rootmysql mysql# mysqladmin -uroot -proot123 flush-logs 7 备份与恢复7.1备份7.1.1单表备份mysqldumprootmysql mysql# mysqldump -uroot -proot123 ITWORKS sys_portletsys_portlet.sqloutfilemysqlselect * from table into outfile /home/mysql/table.txt;7.1.2单库备份rootmysql mysql# my