Home >Database >Mysql Tutorial > 进击的MysqlSlave环境搭建及配置

进击的MysqlSlave环境搭建及配置

WBOY
WBOYOriginal
2016-06-07 17:41:561085browse

一)环境拓扑今天讨论的是mysql集群这一块,资源如下所示:二)Mysql安装配置这里不一一赘述,可以选择源代码编译安装,也可以参考我之前的一篇博文“懒人“速成

一)环境拓扑

二)Mysql安装配置

   这里不一一赘述,可以选择源代码编译安装,也可以参考我之前的一篇博文“懒人“速成——linux LAMP环境。

三)节点配置

主节点:

   1.首先在数据库中建立2个数据库和表:

#service mysqld start #mysql mysql>create database www; mysql>use www; mysql>create table www(id int); mysql>insert into www values(1); mysql> select * from www;

   查看数据: ;

   同理创建blog数据库和表:

mysql>create database blog; mysql>use blog; mysql>create table blog(id int); mysql>insert into blog values(1); mysql> select * from blog;

 进击的MysqlSlave环境搭建及配置


2.修改My.cnf配置文件

vi etc/my.cnf[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin=mysql-bin //日志为2进制,不需要更改 server-id =1 //为1就是Master,不需要更改 binlog-do-db=blog //需要同步的库 binlog-do-db=www //需要同步的库 binlog-ignore-db=mysql,test,information_schema //不需要记录日志的数据库名,多个数据库中间用逗号(,)隔开 innodb_data_home_dir = /usr/local/mysql/data/ //innodb的表空间位置 innodb_data_file_path = ibdata1:50M:autoextend //表空间的名字,开始50M innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_buffer_pool_size = 256M //为系统内存的50-80% innodb_additional_mem_pool_size = 20M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

   3.重启数据库

[root@Test01 /]# service mysqld restart


Stopping mysqld: [ OK ] Starting mysqld: [ OK ]

4.创建有权限的账号,让Slave数据库访问主数据库


[root@Test01 /]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.69-log Source distribution Copyright (c) 2000, 2013, 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>



mysql> grant replication slave on *.* to repl@"192.168.1.26" identified by '123456'; Query OK, 0 rows affected (0.00 sec)



mysql>flush privileges; ;

 5.备份Master数据库


mysql> flush tables with read lock; //不要退出这个终端,否则这个锁就不生效了。 同时取得快照,并记录日志和偏移量: mysql> show master status; +------------------+----------+--------------+-------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+-------------------------------+ | mysql-bin.000001 | 196 | blog,www | mysql,test,information_schema | +------------------+----------+--------------+-------------------------------+ 1 row in set (0.00 sec)

6.开启另一个终端,对主数据库做快照


[root@Test01 mysql]#cd /var/lib/mysql/ [root@Test01 mysql]#tar -zcvf backup.tar.gz www blog

 7.库解锁


mysql> unlock tables;

从节点:

   1.安装mysql,方法同上,不在赘述。

   2.修改My.cnf配置文件


[root@Test02 mysql]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin=mysql-bin server-id = 2 #master-host = 192.168.1.10 #master-user = root #master-password = 584911644 #master-port = 3306 #master-log-pos = 196 #master-log-file = mysql-bin.000001 #master-connect-retry=60 replicate-do-db=www //告诉slave只做www数据库的更新 replicate-do-db=blog //告诉slave只做blog数据库的更新 log-slave-updates [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid "/etc/my.cnf" 23L, 551C

3.把之前主数据库压缩的数据拷贝到对应位置

我这里用的是SecureFX,当然,方法很多,免备案空间,也不细说了,可谓是条条大路通罗马。

4.启动数据库


[root@Test02 mysql]# service mysqld start


Starting mysqld: [ OK ]

5.配置,启动Slave

mysql> slave stop; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='192.168.1.10',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=196; Query OK, 0 rows affected (0.04 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.10 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 339 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 394 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: www,blog Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 339 Relay_Log_Space: 550 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)

四)后期测试

主节点-数据增加:


mysql> insert into www values(2); Query OK, 1 row affected (0.00 sec) mysql> select * from www; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)

从节点数据同步验证:


mysql> select * from www; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)

主节点-数据删除:


mysql> delete from www where id ='2'; Query OK, 1 row affected (0.04 sec) mysql> select * from www; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)

从节点数据同步验证:


mysql> select * from www; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)


综述:

   Test02,从数据库上能做到同步数据库的更新操作,香港虚拟主机,目前是WWW和blog两张表。当然,这里只是抛砖引玉的作用,大家可以举一反三,根据自己实际需求,做出最好的环境搭建和配置。

   最近在弄RHCS和keepalived,闲来有空就记录几笔数据库slave配置,方便大家学习和自己的积累。


本文出自 “Aaron” 博客,虚拟主机,请务必保留此出处

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn