进击的MySQL——Slave环境搭建及配置
进击的MySQL——Slave环境搭建及配置
一)环境拓扑
今天讨论的是mysql集群这一块,资源如下所示:
二)Mysql安装配置
这里不一一赘述,可以选择源代码编译安装,也可以参考我之前的一篇博文“懒人“速成——linux LAMP环境。
三)节点配置
主节点:
1.首先在数据库中建立2个数据库和表:
1 2 3 4 5 6 7 |
#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数据库和表:
1 2 3 4 5 |
mysql>create database blog;
mysql> use blog;
mysql>create table blog(id int );
mysql>insert into blog values( 1 );
mysql> select * from blog;
|
2.修改My.cnf配置文件
1 |
vi etc/my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[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.重启数据库
1 |
[root@Test01 /]# service mysqld restart
|
1 2 |
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
|
4.创建有权限的账号,让Slave数据库访问主数据库
1 2 3 4 5 6 7 8 9 10 11 |
[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>
|
1 2 |
mysql> grant replication slave on *.* to repl@ "192.168.1.26" identified by '123456' ;
Query OK, 0 rows affected ( 0.00 sec)
|
1 2 |
mysql>flush privileges;
;
|
注意:格式:GRANT REPLICATION SLAVE ON *.* TO '帐号'@'从服务器IP或主机名' IDENTIFIED BY '密码';
5.备份Master数据库
1 2 3 4 5 6 7 8 9 |
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)
|
|
评论暂时关闭