mysqld_multi实现多主一从复制

opo

目的：两台主mysql数据库复制到一台从mysql 服务器上

     两台主库的ip为 172.16.27.23
                            172.16.27.25

    一台从库的ip为 172.16.27.21


     1. 在两台主库的配置文件内加入下面几条，两台都要加

server-id = 1
log-bin = mysql-bin
binlog_format=mixed



     2. 加完后保存退出，登录两台数据库建立用来同步的用户并给他授权

GRANT REPLICATION SLAVE ON * . * TO 'REPL' @ ' 172.16.27.21' IDENTIFIED BY '123456';


    做完授权后，重新启动mysql ，查看binlog 是否已经开始生效

   3. 然后在两台主mysql数据库锁表 并记录 pos值和当前的binlog文件
      
   172.16.27.23:


mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000070 |   217643 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)



   172.16.27.25:


mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000002 |    13407 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)



   4.  用mysqldump 或者其他方法将要同步的两台主mysql数据库导出 。

   5. 修改从库的my.cnf 配置文件


[mysqld_multi]
mysqld            = /usr/local/mysql/bin/mysqld_safe
mysqladmin     = /usr/local/mysql/bin/mysqladmin
user                = root
password         =123456
log                 = /usr/local/mysql/mysqld_multi.log

[mysqld1]
port            = 3307
socket          = /tmp/mysql.sock1
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8

pid-file        = /usr/local/mysql/data1/mysqld1.pid
datadir         = /usr/local/mysql/data1
log             =/usr/local/mysql/data1/mysql_multi_1.log
user            = mysql
log-bin         = mysql1-bin
server-id       = 2
master-host     = 172.16.27.23
master-port     = 3306
master-user     = repl
master-password = 123456
replicate-do-db = product
binlog_format   = mixed


[mysqld2]
port            = 3308
socket          = /tmp/mysql.sock2
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8

pid-file        = /usr/local/mysql/data2/mysqld2.pid
datadir         = /usr/local/mysql/data2
log             =/usr/local/mysql/data2/mysql_multi_2.log
user            = mysql
log-bin         = mysql2-bin
server-id       = 3
master-host     = 172.16.27.25
master-port     = 3306
master-user     = repl
master-password = 123456
replicate-do-db = my_blog
binlog_format   = mixed



   6.初始化数据库

/usr/local/mysql/bin/mysql_install_db --datadir=/usr/local/mysql/data1 --user=mysql &
/usr/local/mysql/bin/mysql_install_db --datadir=/usr/local/mysql/data2 --user=mysql &


   
  7. 启动mysql ，这时就不能按照以前那样启动了，现在启动时候需要 mysqld_multi这个程序 具体做法如下

/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 1-2
/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 1,2 这样也可以



  8.下一步就是要导入数据库了，不过要先登录mysql建立数据库

mysql -u root -S /tmp/mysql.sock1
mysql> create database product;
mysql>exit
mysql -u root -S /tmp/mysql.sock1 produt < /root/product.sql

mysql -u root -S /tmp/mysql.sock2
mysql> create database my_blog;
mysql>exit
mysql -u root -S /tmp/mysql.sock2 produt < /root/my_blog.sql



9 .重启两个mysql

/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf report 1,2



10 .重启后分别进入两个进程来启动同步。

TOUVE

男人有冲动可能是爱你，也可能是不爱，但没有冲动肯定是不爱!

96818

避孕的效果：不成功，便成“人”。

look321

有事秘书干，没事干秘书!

buhong

我抢、我抢、我抢沙发~

忧郁者

男人靠的住，母猪能上树!

色粉盒撒娇

写的真的很不错