1、(主库)修改/etc/my.cnf配置文件
innodb_strict_mode=on
#innodb 共享表空间设置
innodb_file_per_table = 1
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:6500M:autoextend
#innodb 日志相关
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 4
innodb_log_group_home_dir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
#innodb 内存相关参数
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size = 32M
innodb_thread_concurrency =0
innodb_flush_method = O_DIRECT
expire_logs_days = 5
log_bin = /var/lib/mysql/mysql-bin
log_bin_index = /var/lib/mysql/mysql-bin.index
2、(从库)修改/etc/my.cnf配置文件
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
binlog_format=mixed
server-id=50 //从库注意一定要修改此参数
lower_case_table_names=1
log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2
max_connections=500
binlog-ignore-db=mysql #被忽略的数据库
#innodb 共享表空间设置
innodb_file_per_table = 1
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:6500M:autoextend
#innodb 日志相关
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 4
innodb_log_group_home_dir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
#innodb 内存相关参数
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size = 32M
innodb_thread_concurrency =0
innodb_flush_method = O_DIRECT
log_slave_updates=1
expire_logs_days = 5
log_bin = /var/lib/mysql/mysql-bin
log_bin_index = /var/lib/mysql/mysql-bin.index
relay_log = /var/lib/mysql/replay-bin
relay_log_index = /var/lib/mysql/replay-bin.index
3、修改主库innodb日志大小
[root@dsdb-1 mysql]# ls ib_logfile*
ib_logfile0 ib_logfile1
server mysql stop
mv ib_logfile* /root
server mysql start
4、(主库)拷贝主库文件到备库
由于主库是已经运行很久的一个库,数据量在100G以上,在综合考虑mysqldump 备份恢复需要很久的时间,采用直接拷贝文件到备库的方式建立slave
由于主库包含InnoDB表的拷贝:
4.1在master主机上使用mysql命令行客户端,并阻塞所有的写入操作
mysql> FLUSH TABLES WITH READ LOCK;
4.2记录偏移量值
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000056 | 107 | | |
+------------------+----------+--------------+------------------
4.3、在另外一个shell客户端关闭master数据库
mysqladmin shudown -p
4.4、拷贝并数据文件
mysqldump --all-databases --master-data > /tmp/datadump.db
或者如果数据量比较大的话,可以用直接拷贝目录的方式,也省去了执行insert语句更新索引的开销。
tar czvf /tmp/databack.tar.gz /var/lib/mysql/
4.5 重启mysql master服务
Service mysql start
4.6在master上释放读锁(如果前面执行过FLUSH TABLES WITH READ LOCK;)
mysql> UNLOCK TABLES;
4.7 主库传输文件到备库
Scp /tmp/databack.tar.gz 10.10.12.50:/tmp
在master上设置复制用户及密码
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.10.12.%' IDENTIFIED BY '123456';
如果主库不包含InnoDB表的拷贝,拷贝方式可以考虑,不用停机:
1 在master主机上使用mysql命令行客户端,将所有数据刷进磁盘,并阻塞所有的写入操作
mysql> FLUSH TABLES WITH READ LOCK;
2 拷贝并压缩数据文件
# tar czvf /tmp/databack.tar.gz /usr/local/mysql/data
3 释放读锁
mysql> UNLOCK TABLES;
5、在从机上设置复制
5.1 设置主库,从库的配置文件my.cnf
(1) 需要设置解压出来文件的权限确保slave能够读取或更改它们
Mv mysql mysql_bak
Mv /tmp/databack.tar.gz /var/lib/
tar xzvf databack.tar.gz
mv /var/lib/var/lib/mysql /var/lib/
(2) 删除data目录下的auto.cnf文件及err日志
rm -rf dsdb-1.pid
rm -rf dsdb-1-relay-bin.000021
rm -rf relay-log.info
rm -rf dsdb-1-relay-bin.000022
rm -rf dsdb-1-relay-bin.000023
rm -rf dsdb-1-relay-bin.000024
rm -rf dsdb-1-relay-bin.000025
rm -rf dsdb-1-relay-bin.000026
rm -rf dsdb-1-relay-bin.000027
rm -rf dsdb-1-relay-bin.000028
rm -rf dsdb-1-relay-bin.000029
rm -rf dsdb-1-relay-bin.000030
rm -rf dsdb-1-relay-bin.000031
rm -rf dsdb-1-relay-bin.000032
rm -rf dsdb-1-relay-bin.000033
rm -rf dsdb-1-relay-bin.index
rm -rf dsdb-1-relay-bin.000034
rm -rf mysql-bin.000052
rm -rf mysql-bin.index
rm -rf master.info
rm -rf dsdb-1.err
rm -rf mysql-bin.000053
5.2 启动mysql服务
Service mysql start
如果在master上加读锁(如果前面执行过FLUSH TABLES WITH READ LOCK;)从库上执行
mysql> UNLOCK TABLES;
mysql> stop SLAVE;
5.3 从库上执行change master操作
mysql> CHANGE MASTER TO master_host='10.10.12.49', master_port=3306, master_user='repl',master_password='123456', master_log_file='mysql-bin.000056', master_log_pos=107;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO master_host='10.10.12.49', master_port=3306, master_user='repl',master_password='123456', master_log_file='mysql-bin.000056', master_log_pos=107;
Query OK, 0 rows affected (0.02 sec)
8.4 从库启动复制进程
mysql> START SLAVE;
9、(从库)检查同步情况
show slave status \G;
验证:
主库:
mysql> create database t;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use t;
Database changed
mysql> create table t(id int,a varchar(10)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED
-> ;
Query OK, 0 rows affected (0.24 sec)
mysql> insert into t values(1,'a');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t values(2,'a');
Query OK, 1 row affected (0.07 sec)
mysql> insert into t values(3,'a');
Query OK, 1 row affected (0.05 sec)
从库 查看:
mysql> select * From t;
+------+------+
| id | a |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
+------+------+
3 rows in set (0.00 sec)
至此主从master-slave已经建立起来了