mycat三步曲之一(数据库主从配置)
最近mycat非常的火热,单网上的资料非常的少.只有一本权威指南,不过指南比较多,不容快速入门,这里小整理了一些资料供大家入门使用!!
第一节 Mysql配置主从
1、编辑mysql的配置文件my.cnf
vi /etc/my.cnf
在[mysqld]下面增加
character_set_server = utf8
log-bin = mysql-bin
binlog-ignore-db = mysql
server-id = 1
在[client]下添加
default-character-set=utf8
2、重启mysql 加载新配置文件
[root@i ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3、查看mysql server级别的编码集
首先进入mysql使用命令 mysql -uroot –p
然后 执行命令查看:
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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
4,为从服务器建立一个slave用的帐号。
# mysql -u root -p
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;
mysql> quit
5,锁库,备份数据库并查看状态
加锁
mysql> flush tables with read lock;
备份数据库
#mysqldump -uroot -p123456 ceshi > ceshi.sql
查看并记录主库状态,并记录标红的值
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 7260
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
ERROR:
No query specified
解锁
mysql> unlock tables
6、编辑从库mysql的配置文件my.cnf
vi /etc/my.cnf
在[mysqld]下面增加
character_set_server = utf8
log-bin = mysql-bin
binlog-ignore-db = mysql
server-id = 1
在[client]下添加
default-character-set=utf8
重启mysql从库
[root@i ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
7、还原备份文件到从数据库
Mysql>mysql -uroot -p123456 ceshi<ceshi.sql
8,准备同步,需用到上面的记录的值
Mysql>change master to master_host='10.165.59.91',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=7260
开始同步
Mysql>start slave;
查看主从状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 10.164.23.01
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 327530772
Relay_Log_File: iZ28md7xZ-relay-bin.37957620
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 327530772
Relay_Log_Space: 564
Until_Condition: None
Until_Log_File:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 310760
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.05 sec)
ERROR:
主要看这两项：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置完成后注意给mycat的所在的IP授予root用户的访问权限.
