Mysql部署与运维

 

Mysql部署与运维

 

 

 

 

1安装

 

1.1准备

 

• 安装linux服务器

 

(略)

 

• 下载MySQL 5.5.28源码包

下载地址：http://www.mysql.com/downloads/mysql/#downloads

在MySQL下载页面的下拉框选择“Source Code”，然后选择最下面的tar.gz格式，开始下载。

 
   
 

 

注意:若linux服务器可连外网,可通过wget方式进行下载：http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.28.tar.gz这个地址。

 

• 添加MySQL用户和组

sudo groupadd mysql

sudo useradd -r -g mysql mysql

 

• 安装一些需要的软件包

一般情况下,需要安装下述软件包,gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool*

 sudo apt-get install cmake automake autoconf libtool gcc g++ bison

 

一般安装上述软件包就可以了,各服务器环境有所不同,需在编译mysql过滤中根据提示动态安装其他相关信赖软件包.

 有时候在编译过程总会出现这样的错误：-- Could NOT find Curses (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH)  CMake Error at cmake/readline.cmake:83 (MESSAGE): Curses library not found.  Please install appropriate package, remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.，则需要多安装libncurses5-dev包,并删除目录下的　CMakeCache.txt 文件，重新进行cmake编译。

 

 sudo apt-get install libncurses5-dev

 

1.2编译安装

 

建议采用源码编译安装：

 

• 可以选择安装需要使用的功能

• 更好的兼容系统

• 指定安装目录

• 一次编译安装后可以用于其他相同配置的服务器

• 编译安装三步, cmake / make / make install

解压安装包,并进入安装包目录.

 
    
 

#创建目录

 sudo mkdir/usr/loca/mysql3

 

#开始编译(注意下面的命令是一句，实际情况是没有换行的，这里为了方便添加注释)

 sudo cmake

 -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3 #安装路径

 -DMYSQL_DATADIR=/usr/local/mysql3/data            #数据文件存放位置

 -DSYSCONFDIR=/usr/local/mysql3                       #my.cnf路径

 -DWITH_MYISAM_STORAGE_ENGINE=1          #支持MyIASM引擎

 -DWITH_INNOBASE_STORAGE_ENGINE=1        #支持InnoDB引擎

 -DWITH_MEMORY_STORAGE_ENGINE=1          #支持InnoDB引擎

 -DWITH_READLINE=1                       #快捷键功能(我没用过)

 -DMYSQL_UNIX_ADDR=/tmp/mysqld3.sock      #连接数据库socket路径,同一台服务器多个mysql时,需避免冲突,可网上搜索其sock文件作用

 -DMYSQL_TCP_PORT=3312                   #端口同一台服务器多个mysql时,需避免冲突

 -DENABLED_LOCAL_INFILE=1                #允许从本地导入数据

 -DWITH_PARTITION_STORAGE_ENGINE=1       #安装支持数据库分区

 -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk   #安装需要的字符集

 -DDEFAULT_CHARSET=utf8                  #默认字符

 -DDEFAULT_COLLATION=utf8_general_ci     #默认字符集

 

  
 

 编译过后,你会发现目录下多了下述文件,若各服务器配置一样,则可以直接把编译过后的目录复制到其他服务器直接进行下一步安装,无需重新编译.

 
  
 
#make安装(有时候直接sudo make && make install 一次性执行不行,有报错.后来分开执行下面命令就可以了,未找到原因)

 sudo make

 sudo make install

 
  
 

 

1.3配置

 

配置my.cnf ,my.cnf的加载顺序，Linux优先级从高到低/etc/my.cnf->/etc/mysql /my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf，高优先级的my.cnf设置会覆盖低优先级的my.cnf，所以一般把config文件copy到安装目录下.

 

sudo cp support-files/my-medium.cnf ./my.cnf

 

 

 #设置权限

 sudo chmod +x /usr/local/mysql 3

 sudo chown -R mysql.mysql /usr/local/mysql 3

 

  #配置开机自启动

 sudo cp /usr/local/mysql3/support-files/mysql.server /etc/init.d/mysql 3

 sudo chmod +x /etc/init.d/mysql 3

 sudo chkconfig –list

 sudo update-rc.d mysql3 defaults `/sbin/chkconfig –add mysql3; /sbin/chkconfig mysql3 on`

 sudo chkconfig –list mysql 3

 

#修改my.cnf配置

 vim ./my.cnf

 

#[mysqld] 添加：

 datadir=/usr/local/mysql3/data

 default-storage-engine=MyISAM

 

#以下可选：

 log-error = /usr/local/mysql3/log/error.log

 pid-file = /usr/local/mysql3/mysql.pid

 user = mysql

 tmpdir = /tmp

 

 

1.4初始化

 第一次启动mysql时,需初始化mysql,不然启动时会出现下述异常:

 Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

 

#安装默认数据表(注意:下述参数为英文的两个 “--”,非中文输入,否则提示很怪的异常)

 sudo scripts/mysql_install_db --basedir=/usr/local/mysql3 --datadir=/usr/local/mysql3/data --user=mysql

 
  
 

 

1.5验证

#启动MySQL

 sudo /etc/init.d/mysql3 start （sudo /etc/init.d/mysql3 –help 查看帮助）

 

#测试MySQL是否启动

 # 1）查看是否有进程mysql

 ps -ef | grep mysql

 # 2）查看端口是否运行

 netstat -tnl | grep 3306

 # 3）读取mysql版本信息

 mysqladmin version

 ##安装成功，Thanks God!

 
  
 

启动过程中,遇到下述异常:

 Can＇t find file: ＇./mysql/host.frm＇

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

 Q: mysqld_safe启动不成功，查看/var/log/mysqld.log有以下错误：

 061110 9:44:16 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13)

 061110 9:44:16 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13)

 

但查看存在/var/lib/mysql/mysql/host.frm这个文件，如何解决？

  A: 这是因为权限的问题，查看/var/lib/mysql/mysql/ 下的文件的用户组都是属于root.root

 把/var/lib/mysql/mysql/ 这个目录删掉，然后用mysql_install_db --user=mysql重新初始化一次mysql，再查看/var/lib/mysql/mysql/ 下的文件的用户组都是属于mysql.mysql, 这时启动mysqld_safe即可正常启动了。

 B: 权限问题，设置mysql　安装目录权限。chown -R mysql.mysql ./mysql3

 

 

1.6设置帐号

为root帐户设置初始密码	#bin/mysqladmin -uroot password 'test' -S /tmp/mysqld.sock
命令行登录mysql	#bin/mysql -uroot -ptest -S /tmp/mysqld.sock
删除密码为空的账号	mysql>use mysql;
	mysql>delete from user where password="";
添加数据库访问账号	mysql>GRANT ALL PRIVILEGES ON *.* TO 'ktjava'@'192.168.%' IDENTIFIED BY 'kt'; 如果为从库则只分配只读权限 mysql>GRANT SELECT,PROCESS ON *.* TO 'ktjava'@'192.168.%' IDENTIFIED BY 'kt';
	mysql>flush privileges;
查看当前的mysql账号	mysql>select Host,User,Password from user;

 

 

1.7安装常见问题

 1.数据目录没有赋予mysql权限

 #chown mysql:mysql -R data db-log

 

 2.多个mysql冲突

 主要是my.cnf文件冲突，可以通过执行:

 #bin/my_print_defaults查看mysql选择my.cnf的优先级：

 默认最先使用/etc/my.cnf;

 优先顺序为:/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql3/my.cnf ~/.my.cnf

 所以一台机器部署多个mysql避免/etc/my.cnf文件的存在;这样各mysql默认使用$basedir/my.cnf

 

3.my.cnf权限不正常

 #chmod 600 my.cnf

 

 

2参数优化

 

1.设置一个磁盘性能较好的目录作为mysql临时空间

 tmpdir = /data/mysqls/tmp

 2.设置最大链接数

 max_connections = 3000

 3.设置通信缓冲区的最大长度

 max_allowed_packet = 10M

 4.当自动扩展表空间被填满之时，每次扩展空间的大小，默认值是8(单位MB)。

 innodb_autoextend_increment = 80

 5.设置内存缓冲池大小，用于缓存表和索引数据等。尽可能的大

 innodb_buffer_pool_size = 2048M

 key_buffer_size = 512M

 

6.日志文件设置

 innodb_log_file_size = 256M

 innodb_log_buffer_size = 8M

 

7.设置线程数CPU's*2

 thread_concurrency = 16

 innodb_thread_concurrency = 16

 innodb_write_io_threads = 16

 innodb_read_io_threads = 16

 

8.设置可以复用的保存在线程池中的线程的数量。

 thread_cache_size=32

 

9.设置MySQL暂时停止回答新请求时间里存在堆栈中的请求数。

 back_log=200

 

10.设置一些缓存的大小

 query_cache_size = 256M

 sort_buffer_size=32M

 read_buffer_size = 16M

 read_rnd_buffer_size =16M

 

3主从搭建

 

3.1原理

 

MYSQL主从同步架构是目前使用最多的数据库架构之一，尤其是负载比较大的网站，因此对于主从同步的管理也就显得非常重要，新手往往在出现主从同步错误的时候不知道如何入手，这篇文章就是根据自己的经验来详细叙述mysql主从的管理。

 

MYSQL主从同步的作用

 

(1) 数据分布
(2) 负载平衡(load balancing)
(3) 备份
(4) 高可用性(high availability)和容错

 

关于MYSQL的主从同步，最主要的是要了解MYSQL的主从同步是如何工作的也即主从同步的原理，通过下图能很明白的指导其工作的过程：

 

  
 

 

大致描述一下过程：从服务器的IO线程从主服务器获取二进制日志，并在本地保存为中继日志，然后通过SQL线程来在从上执行中继日志中的内容，从而使从库和主库保持一致。主从同步的详细过程如下：

 1. 主服务器验证连接。

 2. 主服务器为从服务器开启一个线程。

 3. 从服务器将主服务器日志的偏移位告诉主服务器。

 4. 主服务器检查该值是否小于当前二进制日志偏移位。

 5.  如果小于，则通知从服务器来取数据。

 6.  从服务器持续从主服务器取数据，直至取完，这时，从服务器线程进入睡眠，主服务器线程同时进入睡眠。

 7. 当主服务器有更新时，主服务器线程被激活，并将二进制日志推送给从服务器，并通知从服务器线程进入工作状态。

 8. 从服务器SQL线程执行二进制日志，随后进入睡眠状态。

 

 

常见的集群模式有：

 MySQL Cluster；

 主--主；

 主--从；

 主--从--从；

 主--从--从—主；

 

而较为常用的为主—从--从集群模式.

 

 

3.2搭建实战

 

实战主—从--从集群模式.

 3.2.1环境说明

 

操作系统环境：Ubuntu 12.04 32 bit

 MYSQL版本：MYSQL 5.5.29

 

3.2.2主从mysql安装

 

在同一台服务器下,安装三个mysql,分别为:

 主1: /usr/local/mysql1 port:3310 sock:/tmp/mysqld.sock

 从2: /usr/local/mysql2 port:3311 sock:/tmp/mysqld2.sock

 从3: /usr/local/mysql3 port:3312 sock:/tmp/mysqld3.sock

 

提示:若已安装了mysql3,则可以直接复制出mysql1 mysql2,并且修正对应的my.cnf(数据目录\主目录\端口号\socket路径等) 则可以,省事.同时也需修改/etc/init.d/mysql脚本中的basedir / datadir

 

3.2.3主库配置

 

1.开启binlog

 server-id=1 #集群id,避免冲突

 binlog_format=mixed

 log-bin=/usr/local/mysql1/binlog/mysql-bin

 

2.设置同步数据的账号

 mysql>use mysql;

 mysql> grant replication slave on *.* to 'slave'@'%' identified by 'slave';

 mysql>flush privileges;

 mysql>select Host,User,Password from user;

 

3.设置需要打印binlog的数据库

 设置需要同步的数据库(多个写多行)

 binlog-do-db=center

 设置不需要同步的数据库

 binlog-ignore-db=mysql

 

3.2.4从2库配置

 

注意:第一次时,建议手工先把主库的数据同步一次到从库,以初始化从库数据.建议选用dump方式. #bin/mysqldump –C -h源库** |bin/mysql 目标库***.

 

bin/mysqldump -C -uroot -ptest -S/tmp/mysqld1.sock center|bin/mysql -uroot -ptest -S/tmp/mysqld2.sock center;

 

 1.设置一个集群中唯一的server-id

 

server-id=2

 

2.开启binlog

 

binlog_format=mixed

 

log-bin=/usr/local/mysql2/binlog/mysql-bin

 

3.设置需要打印binlog的数据库

 

设置需要同步的数据库(多个写多行)

 

binlog-do-db=center

 

设置不需要同步的数据库

 

binlog-ignore-db=mysql

 

4这里需要配置下一级从库,因此需要设置log_slave_updates=1以便同步主库数据时打印binlog

 

log_slave_updates=1

 

5.设置同步数据的账号

 

mysql>use mysql;

 

mysql> grant replication slave on *.* to 'slave'@'%' identified by 'slave';

 

mysql>flush privileges;

 

mysql>select Host,User,Password from user;

 

5.设置需要打印binlog的数据库

 

设置需要同步的数据库(多个写多行)

 

binlog-do-db=center

 

设置不需要同步的数据库

 

binlog-ignore-db=mysql

 

6.设置主从

 

change master to master_host='主库ip', master_port=主库端口, master_user='rep_kt', master_password='kt', master_log_file='主库基础备份时的bin-log的file', master_log_pos=主库基础备份时的bin-log的Position;

 

file与position可通过mysql>show master status \G 命令查看.

 
 
 

 

 

mysql>change master to master_host='127.0.0.1', master_port=3310, master_user='slave', master_password='slave', master_log_file='mysql-bin.000001', master_log_pos=647;

 

 

mysql>start slave;

 

3.2.5从3库配置

 

注意:第一次时,建议手工先把主库的数据同步一次到从库,以初始化从库数据.建议选用dump方式. #bin/mysqldump –C -h源库** |bin/mysql 目标库***.

 bin/mysqldump -C -uroot -ptest -S/tmp/mysqld2.sock center|bin/mysql -uroot -ptest -S/tmp/mysqld3.sock center;

 

1.设置一个集群中唯一的server-id

 server-id=3

 2.设置需要同步的数据库或表(不设置则同步从2 过来的binlog所有数据库或表)

 replicate-do-db=center

 

3.2.6同步验证

 

• 先分别为主1\从2\从3 创建数据库center;

• 在主库创建数据表,并插入数据.

• 查看各从库数据状态,是否正常同步.

• 查看各从库同步状态

 

mysql>show slave status \G

 

 
  
 

 

查看Slave_IO_Running: Yes；Slave_SQL_Running: Yes是否都为Yes。刷新几次该命令查看Read_Master_Log_Pos: 627827289是否会增大。如果非以上状态则slave同步失败，需要根据错误日志查找原因

 

 

4管理与维护

 

4.1权限管理

 

1.设置为读写权限

 

mysql>GRANT ALL PRIVILEGES ON *.* TO 'jbjava'@'192.168.%' IDENTIFIED BY 'jb98';

 

2.设置只读权限

 

mysql>GRANT SELECT ON versiondb5.* TO 'db_version'@'192.168.%' IDENTIFIED BY 'jb_version';

 

3.设置主从复制权限

 

mysql>GRANT REPLICATION SLAVE ON *.* TO 'rep_jb'@'192.168.%' identified by 'jb98';

 

4.使权限生效

 

mysql>flush privileges;

 

 

说明：

 

从库一般设置为只读权限，以免从库因为写入数据而导致主从失败数据部一致。

 

4.2数据备份

 

从库虽然可以能够在主库宕机的情况下可以切换为主库，但是不可避免一些人为失误delete掉数据造成的损失。

 

 

全量备份：

 

mysqldump

 

直接备份data文件

 

 

每天定时备份并将数据拷贝到其他服务器：

 

bin/mysqldump -C -uroot -ptest -S /tmp/mysqld1.sock --all-databases | gzip > /var/log/jbmon/backup/db/mysql_1_$yesterday.sql.gz

 

4.3数据恢复

 

1.通过备份数据恢复：

 

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

 

 

2.同步data目录数据恢复：

 

覆盖data目录-设置mysql权限-删除data下的pid文件

 

 

3.通过binlog恢复：

 

bin/mysqlbinlog /db-log/binlog.xxxx | mysql -uroot -pjb_gt0 -S /tmp/mysqld_xxxx.sock

 

=>可以指定恢复某个时间点的数据修改

 

4.4数据迁移

 

说明	操作
停止mysql	#/etc/init.d/mysql stop
	#vi /etcinit.d/mysql
修改data目录	datadir=/xxxx/xxxx/data
迁移data目录	mv data /xxxx/xxxx/
修改data的权限	#chown mysql:mysql -R /xxxx/xxxx/data
启动mysql	#init.d/mysql start

 

 

5监控与故障处理

 

5.1监控

 

1.检测mysql服务状况

 

bin/mysql –u** -p** testdb –e ‘select 1 from testdb’

 

 

2.检测主从同步是否正常

 

show slave status;

 

检测Slave_IO_Running: Yes；Slave_SQL_Running: Yes是否都为Yes

 

 

3.检测主从同步是否延迟

 

获取主库master status的binglog文件和position；获取从库同步状态的binlog文件和position

 

对比文件名是否一致及position延迟的数量是否太大

 

5.2常见故障

 

1.主库宕机

 

如果重启无效，则选择一个从库切换为主库

 

2.主从同步失败

 

（1）在从库修复报错的数据，然后执行slave start;

 

（2）mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;跳过某一报错的事务;

 

（3）从主库手动同步一次该表的数据;然后执行slave start

 

bin/mysqldump 主库** db table |bin/mysql 从库** db

 

3.数据乱码

 

vi my.cnf设置character-set-server = utf8

 

4. Mysim表损坏

 

先备份损坏的表文件

 

bin/myisamchk --recover --quick /path/to/tblName

 

bin/myisamchk --recover /path/to/tblName

 

bin/myisamchk --safe-recover /path/to/tblName