读写分离二

目标:读写分离二之主从同步

效果:实现mysql主库的写入之后,会自动同步到从库。

环境:

主数据库IP192.168.1.121
从数据库IP192.168.2.94

配置步骤:

1、修改主数据库的的配置文件:

[mysqld]
server-id=1
log-bin=mysqlmaster-bin.log
sync_binlog=1
#/etc/init.d/mysql restart

#注意:下面这个参数需要修改为服务器内存的70%左右
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1


修改之后要重启mysql:

#
 /etc/init.d/mysql restart

2、修改从数据库的的配置文件(server-id配置为大于1的数字即可):

[mysqld]
server-id=2
log-bin=mysqlslave-bin.log
sync_binlog=1
#注意:下面这个参数需要修改为服务器内存的70%左右
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
lower_case_table_names=1
log_bin_trust_function_creators=1

修改之后要重启mysql:

#
 /etc/init.d/mysql restart

3、SSH登录到主数据库:

(1)在主数据库上创建用于主从复制的账户(192.168.1.121换成你的从数据库IP):

#
 mysql -uroot -p
 mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.3' IDENTIFIED BY 'repl';

(2)主数据库锁表(禁止再插入数据以获取主数据库的的二进制日志坐标):

mysql>FLUSH TABLES WITH READ LOCK;


(3)然后克隆一个SSH会话窗口,在这个窗口打开MySQL命令行:

#
 mysql -uroot -p
mysql>
 SHOW MASTER STATUS;
+------------------------+----------+--------------+------------------+-------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000001 |      321 |              |                  |                   |
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>exit;

在这个例子中,二进制日志文件是mysqlmaster-bin.000001,位置是321,记录下这两个值,稍后要用到。

(4)在主数据库上使用mysqldump命令创建一个数据快照:

#mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases  --triggers --routines --events >all.sql

#接下来会提示你输入mysql数据库的root密码,输入完成后,如果当前数据库不大,很快就能导出完成。

(5)解锁第(2)步主数据的锁表操作:

mysql>UNLOCK TABLES;

7、SSH登录到从数据库:
(1)通过FTP、SFTP或其他方式,将上一步备份的主数据库快照all.sql上传到从数据库某个路径,例如我放在了/home/yimiju/目录下;
(2)从导入主的快照:

#cd /mnt
#mysql -uroot -p -h127.0.0.1 -P3306 < all.sql

#接下来会提示你输入mysql数据库的root密码,输入完成后,如果当前数据库不大,很快就能导入完成。

(3)给从数据库设置复制的主数据库信息(注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值):

#mysql -uroot -p
mysql>
 CHANGE MASTER TO MASTER_HOST='192.168.100.2',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=321;
#然后启动从数据库的复制线程:
mysql>
 START slave;
#接着查询数据库的slave状态:
mysql>
 SHOW slave STATUS \G
#如果下面两个参数都是Yes,则说明主从配置成功!
===========自己的测试结果===============
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
=======网上的结果======
Slave_IO_Running:
 Yes
Slave_SQL_Running:
 Yes
(4)接下来你可以在主数据库上创建数据库、表、插入数据,然后看从数据库是否同步了这些操作。
以上配置,亲测成功。

==================遇到的问题=========================

Warning: Using a password on the command line interface can be insecure.  
mysqldump: Got error: 1862: Your password has expired. To log in you must change it using a client that supports expired passwords. when trying to connect  
解决方案:
mysql -uroot -p -h127.0.0.1  
Enter password:   
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 12  
Server version: 5.6.26-log  
  
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.  
  
Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
  
mysql> select user();  
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement  
mysql> set password for 'root'@'127.0.0.1' = password('root');  
Query OK, 0 rows affected (0.05 sec)  

相关文章
相关标签/搜索