MySQL主从搭建

环境简介
master(主）：192.168.12.101
slave(从）：192.168.12.105
主机基本配置：CentOS 6.5 64bit 8G内存 100G硬盘 4核CPU

一、安装软件
1、在两台linux主机上安装mysql，默认的安装位置是/var/lib/mysql
rpm -ivh MySQL-server-5.6.19-1.rhel5.x86_64.rpm
rpm -ivh MySQL-client-5.6.19-1.rhel5.x86_64.rpm
2、添加到自动启动
chkconfig --add mysql
3、启动服务
service mysql start
4、连接数据库并修改初始密码
cat /root/.mysql_secret --初始密码存放的位置
mysql -u root -p --使用初始密码登陆
set password=password('tina'); --修改密码
\q退出，使用新密码登陆
5、先关闭防火墙，以保证从库能正常连接
关闭防火墙：service iptables stop

二、 修改主服务器的配置文件/etc/my.cnf，开启BINLOG，并设置server-id的值，设置完成重启服务
log-bin = mysql-bin
server-id=100
binlog_format = "MIXED"
三、 修改从服务器的配置文件，其中server-id不能与主服务器一样，设置完成重启服务 service mysql restart
log-bin = mysql-bin
server-id=200

--其他重要参数可参考如下---
[mysqld]
port=3306
socket=/var/lib/mysql/mysqld.sock
character_set_server=utf8 ---设置字符集，在5.5之前的版本，参数名有所不同，需注意
log-bin=/var/lib/mysql/mysql-bin
log-error=/var/log/mysql/mysqlerr.log ---记录数据库的错误日志的
#log_slow_queries=on --默认路径/var/lib/mysql/localhost-slow.log --慢查询日志，有需要才会开启
sync_binlog =1
skip-external-locking
skip-name-resolve
symbolic-links=0
max_connections=500 --最大连接数
max_heap_table_size=128m （HEAP数据表的最大长度(默认设置是16M); 超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里）
#for standby --标红的这一段需要添加到从库的配置文件中
#read-only=1
#log_slave_updates=1
#relay-log=/var/lib/mysql/mysql-relay-bin
#relay-log-index=/var/lib/mysql/mysql-relay-bin.index
#skip-slave-start=1
server_id=100
binlog_format=mixed

key_buffer_size = 512M --可用内存的25%-30% 控制索引可以使用的内存，越多越好
open_files_limit=30000
sort_buffer_size=10M --order by group by 分配的内存
read_buffer_size=20M --提高select 查询的速度
read_rnd_buffer_size = 512K
myisam_sort_buffer_size=64M
thread_cache_size=30
thread_concurrency=8
table_open_cache=1800
query_cache_limit=32M
query_cache_size=4M
#innodb_additional_mem_pool_size=4M
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:500M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_open_files = 2048
innodb_file_per_table = 1
innodb_buffer_pool_size = 5120M
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqld_safe]
pid-file=/var/lib/mysql/mysqld.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
确认字符集：
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

四、创建主库复制用户：
grant replication slave,replication client on *.* to repli@'192.168.12.105' identified by "repli"; ---从库也要进行同样的操作，方便日后进行切换

五、主库锁表：
flush tables with read lock;

六、数据的导出导入:
从主库导出：
# mysqldump -uroot -p --all-databases --lock-all-tables>/tmp/all.sql
scp /tmp/all.sql root@192.168.12.105:/tmp
导入从库：
# mysql -u root -p </tmp/all.sql

七、配置主库连接信息：
主库操作：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 427 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.13 sec)
从库操作：
mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to ---配置从库到主库的连接信息
master_host='192.168.10.197',
master_user='repli',
master_password='repli',
master_log_file='mysql-bin.000007', ---主库正在写的binlog日志文件
master_log_pos=427; ---主库正在写的binlog日志位置

八、创建备库复制用户：
grant replication slave,replication client on *.* to repli@'192.168.12.101' identified by "repli";

九、开始复制
主库操作：
mysql> unlock tables; ---解锁主库
Query OK, 0 rows affected (0.00 sec)
从库操作：
mysql> start slave; ---从库开启复制
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G; ---查看从库状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.197
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 427
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
同步正常

十、检测是否正常复制
主库操作：
mysql> show slave hosts; --可以看到从库的主机信息
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 200 | | 3306 | 100 | c954f3a2-21d0-11e4-a4b2-000c2981e58a |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+--------+
| name |
+--------+
| liting |
| tina |
+--------+
2 rows in set (0.02 sec)
mysql> insert into t1 values ('haha'),('heihei');
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0
从库操作：
mysql> select * from t1;
+--------+
| name |
+--------+
| liting |
| tina |
| haha |
| heihei |
+--------+
4 rows in set (0.00 sec)
主从搭建完成！



常用的与热备有关的mysql命令：
stop slave 停止同步
start slave 开始同步，从日志终止的位置开始更新
set sql_log_bin=0|1 主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误。
set global sql_slave_skip_counter=n 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。
reset master 主机端运行，清除所有的日志
reset slave 从机运行，清除日志同步位置标志，并重新生成master.info 可能并不起用，最好重启mysql进程。
load table a from master 从主机端重读指定表的数据，每次只能读取一个，受timeout
时间限制，需要调整timeout时间，执行这个命令需要同步账号有reload和super权限，以及相应库的
select 权限，如果表比较大，要增加net_read_timeout和net_write_timeout的值
load data from master 从主机端重新读入所有的数据。
show master status 主机运行，看日志导出信息
show slave status 从机运行，查看从库状态
show slave hosts 主机运行，看连入的从机的情况
show master logs
purge master 将在一个指定的日期或日志名之前，删除主服务器上的二进制日志。这个命令用于用户希望确定仍然需要处理哪些文件的情况。
purge master logs to binary-log 或者 purge master logs before date