centos6.5下mysql-5.6.19数据库安装、优化、互为主从 mysql集群 高可用

mysql互为主从环境介绍：

系统：centos6.5_64_mini
mysql版本：mysql-5.6.19
实验节点如下：

节点一：192.168.100.21
节点二：192.168.100.22
 
第一部分安装需要用到的库并删除自带mysql （节点一和二）

#  yum -y install gcc gcc-c++ ncurses ncurses-devel openssl openssl-devel cmake perl lsof wget bison
#  rpm -qa|grep mysql
#  rpm -e --allmatches --nodeps mysql-libs-5.1.71-1.el6.x86_64
 
第二部分MYSQL的安装（节点一和二）

1：创建MySQL数据库存放目录、配置用户和用户组
#  /usr/sbin/groupadd mysql
#  /usr/sbin/useradd -s /sbin/nologin -M -g mysql mysql
#  mkdir -p /yunwei8/mysql/data/
#  chown -R mysql:mysql /yunwei8/mysql/
 
2：创建软件下载目录，把所需软件下载

#  mkdir /yunwei8/soft
#  cd /yunwei8/soft
#  wget http://down.yunwei8.com/soft/linux/mysql-5.6.19.tar.gz
#  wget http://down.yunwei8.com/soft/linux/libunwind-1.1.tar.gz
#  wget http://down.yunwei8.com/soft/linux/gperftools-2.2.tar.gz
 
3：安装mysql-5.6.19

#  tar zxvf mysql-5.6.19.tar.gz
#  cd mysql-5.6.19
#  cmake -DCMAKE_INSTALL_PREFIX=/yunwei8/server/mysql -DMYSQL_DATADIR=/yunwei8/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc/ -DWITH_SSL=yes -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=on
#  make
#  make install
#  chmod +w /yunwei8/server/mysql
#  chown -R mysql:mysql /yunwei8/server/mysql/
 
4：创建软连接

#  ln -s /yunwei8/server/mysql/lib/lib* /usr/lib/
 
5：创建my.cnf配置文件，并修改：

设置Mysql
#  cp /yunwei8/server/mysql/support-files/my-default.cnf /etc/my.cnf
#  vi /etc/my.cnf
 
在[mysqld]下添如下行
character-set-server = utf8
default-storage-engine = MyISAM
basedir = /yunwei8/server/mysql
datadir = /yunwei8/mysql/data
log-error = /yunwei8/mysql/mysql_error.log
pid-file = /yunwei8/mysql/mysql.pid
max_allowed_packet = 32M
explicit_defaults_for_timestamp = true
 
6：以mysql用户帐号的身份建立数据表：

#  /yunwei8/server/mysql/scripts/mysql_install_db --basedir=/yunwei8/server/mysql --datadir=/yunwei8/mysql/data --user=mysql
 
7：设置mysql开机自动启动服务

#  cp /yunwei8/server/mysql/support-files/mysql.server /etc/init.d/mysqld
#  chkconfig --add mysqld
#  chkconfig --level 345 mysqld on
 
8：修改服务配置文件

#  vi /etc/init.d/mysqld
 
根据设定需要，修改mysqld文件中的下面两项
basedir=/yunwei8/server/mysql
datadir=/yunwei8/mysql/data
 
9：启动mysqld服务

#  service mysqld start
 
10：通过命令行登录管理MySQL服务器并设置root密码（提示输入密码时直接回车）：（节点一和二）
#  /yunwei8/server/mysql/bin/mysql -u root -p -S /tmp/mysql.sock
#  mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('000000');
 
退出mysql
#  mysql> exit
 
11：软连接mysql
#  ln -s /yunwei8/server/mysql/bin/mysql /usr/bin
　
12：尝试用root连接mysql
#  mysql -u root -p
 
成功登录后查看状态
#  mysql> status;
 
第三部分：mysql优化（节点一和二）

为MySQL添加TCMalloc库的安装步骤（Linux环境）：
1：64位操作系统请先安装libunwind库，32位操作系统不要安装。libunwind库为基于64位CPU和操作系统的程序提供了基本的堆栈辗转开解功能，其中包括用于输出堆栈跟踪的API、用于以编程方式辗转开解堆栈的API以及支持C++异常处理机制的API。
首先安装libunwind-1.1

#  cd /yunwei8/soft
#  tar zxvf libunwind-1.1.tar.gz
#  cd libunwind-1.1
#  CFLAGS=-fPIC ./configure
#  make CFLAGS=-fPIC
#  make CFLAGS=-fPIC install
 
2：安装google-perftools：

#  cd /yunwei8/soft
#  tar zxvf gperftools-2.2.tar.gz
#  cd gperftools-2.2/
#  ./configure
#  make && make install
#  echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf
#  /sbin/ldconfig
 
3：修改MySQL启动脚本（根据你的MySQL安装位置而定）

#  vi /yunwei8/server/mysql/bin/mysqld_safe
 
在# executing mysqld_safe的下一行，加上：
export LD_PRELOAD=/usr/local/lib/libtcmalloc.so
 
保存后退出，然后重启MySQL服务器。
#  service mysqld restart
 
4：使用lsof命令查看tcmalloc是否起效：
#  /usr/sbin/lsof -n | grep tcmalloc
 
如果发现以下信息，说明tcmalloc已经起效：
mysqld    11227 mysql  mem       REG                8,1  2171746      18287 /usr/local/lib/libtcmalloc.so.4.1.2
 
第四部分：mysql互为主从配置
第一步防火墙设置（节点一和二）
端口开放
#  /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
 
然后保存：
#  /etc/rc.d/init.d/iptables save
 
第二步配置mysql互为主从
1：登录mysql数据库（节点一和二）
#  mysql-u root -p
 
2：建立需要同步的数据库（节点一和二）
#  mysql> create database yunwei8;
 
3：添加一个远程用户，名为yunwei8密码为123456,并且只能对yunwei8数据库有权限（节点一和二）
#  mysql> GRANT ALL PRIVILEGES ON yunwei8.* TO yunwei8@"%" IDENTIFIED BY '123456';
 
4：修改/etc/my.cnf文件（节点一）
#  rm -rf /etc/my.cnf
#  vi /etc/my.cnf
添加如下配置代码
[client]
default-character-set = utf8
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
character-set-server = utf8
default-storage-engine = MyISAM
basedir = /yunwei8/server/mysql
datadir = /yunwei8/mysql/data
log-error = /yunwei8/mysql/mysql_error.log
pid-file = /yunwei8/mysql/mysql.pid
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1
binlog-do-db=yunwei8
replicate-do-db=yunwei8
binlog-ignore-db=mysql
replicate-ignore-db=mysql
sync_binlog=1
auto_increment_increment = 2
auto_increment_offset = 1
log-slave-updates
slave-skip-errors=all
[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
5：修改/etc/my.cnf文件（节点二）
#  rm -rf /etc/my.cnf
#  vi /etc/my.cnf
添加如下配置代码
[client]
default-character-set = utf8
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
character-set-server = utf8
default-storage-engine = MyISAM
basedir = /yunwei8/server/mysql
datadir = /yunwei8/mysql/data
log-error = /yunwei8/mysql/mysql_error.log
pid-file = /yunwei8/mysql/mysql.pid
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id       = 2
binlog-do-db=yunwei8
replicate-do-db=yunwei8
binlog-ignore-db=mysql
replicate-ignore-db=mysql
sync_binlog=1
auto_increment_offset = 2
auto_increment_increment = 2
log-slave-updates
slave-skip-errors=all

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
6：重启两台服务器的mysql（节点一和二）
#  service mysqld restart
 
7：给两个节点授权同步账户, 赋予REPLICATION SLAVE权限（节点一和二）
#  mysql -u root -p
#  mysql> GRANT REPLICATION SLAVE ON *.* to 'abc'@'%' identified by 'abc';
 为abc用户赋予任何数据库中任何表上的REPLICATION SLAVE权限, 此用户可以在网络任意位置访问, 访问时以密码abc标记
 
8：开启一个权限跟ROOT一样的远程账户用于后期脚本监控（节点一和二）
#  GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY '123456';
 
9：锁表（锁表状态下不能退出SSH，否则会失败，使用SSH可以另外克隆一个）（节点一和二）
#  mysql -u root -p
#  mysql> FLUSH TABLES WITH READ LOCK;
 
10：查看节点一状态（记录二进制开始文件，位置）
#  mysql> show master status;
显示：
#  mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1051 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
11：查看节点二状态（记录二进制开始文件，位置）
#  mysql> show master status;
显示：
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      764 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
12：节点一连接节点二
#  mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.22',MASTER_USER='abc', MASTER_PASSWORD='abc',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=764;
 
12.1:开启节点一同步进程
#  mysql> START SLAVE;
 
12.2:查看节点一同步状态是否正常
#  mysql> SHOW SLAVE STATUSG;
 
 *************************** 1. row ***************************
……………………(省略部分)
Slave_IO_Running: Yes  //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

13：节点二连接节点一
#  mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.21',MASTER_USER='abc', MASTER_PASSWORD='abc',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1051;
 
13.1开启节点二同步进程
#  mysql> START SLAVE;
 
13.2查看节点二同步状态是否正常
#  mysql> SHOW SLAVE STATUSG;
 
 *************************** 1. row ***************************
……………………(省略部分)
Slave_IO_Running: Yes  //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

14：解锁节点一和节点二
#  mysql> UNLOCK TABLES;

15：退出节点一和节点二的mysql，至此mysql的互为主从已经实现了
#  mysql> exit;
 
 
第五部分：mysql配置文件详解
[client]
#设置mysql客户端默认字符集
default-character-set = utf8
 
#指定默认端口
port  = 3306
 
#是个UNIX域socket文件，这种socket效率比基于TCP/IP的socket高，但是缺点是不能跨机器连接，mysql提供这个东西是为了改善本地连接的速度
socket  = /tmp/mysql.sock

[mysqld]
#服务端使用的字符集
character-set-server = utf8
 
#创建新表时将使用的默认存储引擎
default-storage-engine = MyISAM
 
#基准路径，其他路径都相对于这个路径
basedir = /yunwi8/server/mysql
 
# mysql数据库文件所在目录
datadir = /yunwei8/mysql/data
 
#错误日志存放路径
log-error = /yunwei8/mysql/mysql_error.log
 
#进程存放位置
pid-file = /yunwei8/mysql/mysql.pid
port  = 3306
socket  = /tmp/mysql.sock
 
#不使用系统锁定，要使用myisamchk,必须关闭服务器
skip-external-locking
 
#myisam索引buffer,只有key没有data
key_buffer_size = 384M
 
#增长到max_allowed_packet字节
max_allowed_packet = 32M
 
#主要用于设置table高速缓存的数量
table_open_cache = 512
 
#排序buffer大小；线程级别
sort_buffer_size = 2M
 
#以全表扫描(Sequential Scan)方式扫描数据的buffer大小 ；线程级别
read_buffer_size = 2M
 
#MyISAM以索引扫描(Random Scan)方式扫描数据的buffer大小 ；线程级别
read_rnd_buffer_size = 8M
 
#MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_sort_buffer_size = 64M
 
#线程缓存
thread_cache_size = 8
 
#查询缓存大小
query_cache_size = 32M
 
#同时运行的线程的数据 此处最好为CPU个数两倍。本机配置为CPU的个数
thread_concurrency = 8
 
#以下为互为主从配置选项
#启用二进制日志
log-bin=mysql-bin
 
#表示是本机的序号为1,一般来讲就是master的意思。
server-id = 1
 
#要记录日志的数据库,如果需要多个重复设置这个选项即可
binlog-do-db=yunwei8
 
#需要复制的数据库名，如果需要多个数据库，重复设置这个选项即可
replicate-do-db=yunwei8
 
#不要记录日志的数据库名，如果需要多个重复设置这个选项即可
binlog-ignore-db=mysql
 
#不需要复制的数据库名，如果需要多个数据库，重复设置这个选项即可
replicate-ignore-db=mysql
 
#默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。
sync_binlog=1
 
#字段一次递增多少
auto_increment_increment = 2
 
#自增字段的起始值，这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID
auto_increment_offset = 1
 
#表示如果一个MASTER挂掉的话，另外一个马上接管；一定要加上，否则不会把更新的记录写到二进制文件里
log-slave-updates
 
#跳过错误，继续执行复制操作
slave-skip-errors=all
[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
#这个配置段设置启动MySQL服务的条件；在这种情况下，no-auto-rehash确保这个服务启动得比较快。
no-auto-rehash