mysql主从复制之多从复制

主库不能停的情况下mysql主从复制之多从复制

 

网上有很多mysql主从复制做法基本上如下：

1、数据库锁表操作，不让数据再进行写入动作。mysql> FLUSH TABLES WITH READ LOCK;

2、察看主数据库的状态 mysql> show master status;

3、把主服务器数据文件复制到从服务器，最好先用tar处理一下。

4、取消主数据库锁定 mysql> UNLOCK TABLES;

5、配置从库

mysql> change master to master_host='192.168.0.1', master_user='rep1', master_password='mysql', master_log_file='dbmaster1-bin.000215', master_log_pos=690097205;

正确执行后再执行:

mysql> start slave;

 

但我这边的情况是：已经有一个主，一个从 主库不能停。我是在从库上做文章

 1.登录从库 锁表然后查master pos号：
mysql> FLUSH TABLES WITH READ LOCK; 

mysql> show slave status\G;       
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: dbmaster1.*****.com.cn
                  Master_User: mmm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: dbmaster1-bin.000215
          Read_Master_Log_Pos: 690097056
               Relay_Log_File: dbslave1-relay-bin.000368
                Relay_Log_Pos: 690097205
        Relay_Master_Log_File: dbmaster1-bin.000215
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 690097056
              Relay_Log_Space: 690097410
              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:

注意上面的红色这个pos号就是锁表后的从库执行主库的pos号，这个号是不变的直到恢复锁表。

 

备份从库，因为我的从库也不能stop 所以用mysqldump导出

上面的锁表状态不能退，最好用screen 进去 执行 然后ctrl+a d  退出

 

[root@dbslave1 mysql]# more dump.sh
#!/bin/bash

for name in `mysql -u root -e "show databases\G"|awk '/Database/{print $2}'`
do
   mysqldump -uroot --opt $name >  $name.dmp
   gzip $name.dmp
done

 

把日志文件及 master.info relay-log.info 备份一下。

 

恢复从库

screen -r 刚才锁表那个screen进程号

mysql> UNLOCK TABLES; 

退出

 

开始在另外一台主机上做第二个从库同步

[root@BETA2 mysql]# more imp.sh
#!/bin/bash

for file in `ls *.dmp|sed 's/\.dmp$//'`
do
   mysql -uroot $file < ${file}.dmp
done

停止mysql服务

service mysqld stop

 

把第一个从库的mysql日志及 master.info relay-log.info 复制过来。

 

然后启动mysql

在my.cnf里加上skip-slave-start 需要手工启动slave

mysql> change master to master_host=dbmaster1.*****.com.cn', master_user='mmm', master_password='mysqlha', master_log_file='dbmaster1-bin.000215', master_log_pos=690097056;

 

mysql> start slave;

 

 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: dbmaster1.*****.com.cn
                  Master_User: mmm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: dbmaster1-bin.000216
          Read_Master_Log_Pos: 356487122
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 356487271
        Relay_Master_Log_File: dbmaster1-bin.000216
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 356487122
              Relay_Log_Space: 356487474
              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)

同步完成.

 

注意：

在从库上做同步及时FLUSH TABLES WITH READ LOCK; 

              Master_Log_File: dbmaster1-bin.000215
          Read_Master_Log_Pos: 690097056
               Relay_Log_File: dbslave1-relay-bin.000368
                Relay_Log_Pos: 690097205
        Relay_Master_Log_File: dbmaster1-bin.000215

加蓝色的是一直会变的，一定要看 Exec_Master_Log_Pos: 690097056 这个号

 

 

=============================================================================== 

错误信息：Last_IO_Error: error connecting to master - retry-time: 60 retries: 86400

解决办法：

slave服务器上删除数据库目录下的master.info、你的主机名-relay-bin.*，重启slave即可。

 

 

-------------------------------------------------------------------

主服务器IP: 10.1.1.3
从服务器IP: 10.1.1.4
一、部署文档
1.确保在主服务器和从服务器上安装的MySQL版本一致.

2.在主服务器上为从服务器设置一个连接账户
mysql> GRANT REPLICATION SLAVE, SUPER, RELOAD ON *.* TO IDENTIF
IED BY 'userpassword';

3. 执行FLUSH TABLES WITH READ LOCK 进行锁表
mysql> FLUSH TABLES WITH READ LOCK；

4. 让客户程序保持运行，发出FLUSH TABLES语句让读锁定保持有效。(如果退出客户程序，锁被释放）。
然后对主服务器上的数据进行快照：
进入主服务器的数据目录，然后执行命令：
shell> tar -cvf /tmp/mysql-snapshot.tar .
将快照文件复制到从服务器主机，然后解压到数据目录：
shell> tar -xvf /tmp/mysql-snapshot.tar
读取主服务器上当前的二进制日志名（File）和偏移量值(Position)，并记录下来：
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动：
mysql> UNLOCK TABLES；

5. 确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log_bin选项
[mysqld]
Log_bin=mysql-bin
server-id=1

6. 停止用于从服务器的服务器并在其my.cnf文件中添加下面的行：
[mysqld]
server-id=2

7. .如果对主服务器的数据进行二进制备份，启动从服务器之前将它复制到从服务器的数据目录中。
确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。

8. 用--skip-slave-start选项启动从服务器，以便它不立即尝试连接主服务器

9. 在从服务器上执行下面的语句，
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.3',MASTER_USER='username',MASTER_PASSWORD='userpassword',
MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;

9. 启动从服务器线程：
mysql> START SLAVE；

10．验证部署是否成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.3
Master_User: rep_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000058
Read_Master_Log_Pos: 27324573
Relay_Log_File: cacti-11-111-relay-bin.000008
Relay_Log_Pos: 27324718
Relay_Master_Log_File: mysql-bin.000058
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 27324573
Relay_Log_Space: 27325025
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
1 row in set (0.00 sec)
当Slave_IO_Running和Slave_SQL_Running都显示Yes的时候,表示同步成功
 

二、切换文档：
1. 确保从服务器已经处理了中继日志中的所有语句。
在从服务器上，执行以下命令
mysql> STOP SLAVE IO_THREAD

2.然后检查SHOW PROCESSLIST语句的输出，直到你看到Has read all relay log。

3.当从服务器都执行完这些，它们可以被重新配置为一个新的设置。

4.在被提升为主服务器的从服务器上，发出STOP SLAVE和RESET MASTER和RESET SLAVE操作.
5. 然后重启mysql服务.

6.在主服务器上RESET MASTER
然后CHANGE MASTER TO MASTER_HOST='10.1.1.4',MASTER_USER='rep_slave',MASTER_PASSWORD='userpassword';
切换完成

 

=========================================================================

mysql主从复制

(一)安装mysql(主从服务器皆相同) 先创建用户 useradd mysql -s /sbin/nologin

 

tar zxvf mysql-5.0.45.tar.gz

cd mysql-5.0.45

./configure --prefix=/usr/local/mysql  --localstatedir=/opt/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client

注:配置过程指定数据文件的位置及另加的字符集.

make

make install

cp support-files/my-large.cnf   /etc/my.cnf

cd /usr/local/mysql

chgrp -R mysql .

生成基本的数据库和表:  /usr/local/mysql/bin/mysql_install_db --user=mysql

成功执行后察看数据目录/opt/data,看是否有文件或目录生成.

chown -R mysql:mysql /opt/data

记得给每个数据库设置root密码.

(二)修改配置文件

不同的地方就是server-id,主服务器配置文件不用修改,从服务器的配置文件server-id=10.其他的内容基本相同.

(三)启动服务

/usr/local/mysql/bin/mysqld_safe --user=mysql&

 这个过程主辅服务器都相同.

(四)授权(在主服务器上进行)

GRANT REPLICATION SLAVE ON *.* to identified by 'mysql';

(五)查询主数据库状态(主服务器上进行)

mysql> show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      235 |              |                  |
+------------------+----------+--------------+------------------+

记下file及position的值,后面做从服务器操作的时候需要用.

(六)配置从服务器

 mysql> change master to master_host='192.168.8.101', master_user='rep1', master_password='mysql', master_log_file='mysql-bin.000003', master_log_pos=235;

正确执行后再执行:

mysql> start slave;

就启用了复制功能.这里我们运行一下 mysql> show slave status\G 来检查一下,一个正常的输出结果应该如下面的形式:

mysql> show slave status\G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.8.101

                Master_User: rep1

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000003

        Read_Master_Log_Pos: 235

             Relay_Log_File: -relay-bin.000009

              Relay_Log_Pos: 235

      Relay_Master_Log_File: mysql-bin.000003

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB:

        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: 235

            Relay_Log_Space: 235

            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

1 row in set (0.00 sec)

 请注意:slave_IO进程及slave_SQL进程都必须正常运行,在状态输出重表现为:   Slave_IO_Running: Yes  及Slave_SQL_Running: Yes 否则都是不正确的状态(如一个值Yes,另外一个是NO则不行).

(七)主数据库有数据的情况:

1、数据库锁表操作，不让数据再进行写入动作。mysql> FLUSH TABLES WITH READ LOCK；

2、察看主数据库的状态 mysql> show master status; 照第（五）步记录输出值。

3、把主服务器数据文件复制到从服务器，最好先用tar处理一下。

4、取消主数据库锁定 mysql> UNLOCK TABLES； 

5、从服务器的操作。跟前面的步骤一样（略过）

 

 

 

=============================================

 

在一台机器上建多个mysql 基于不同端口

 

MYSQL_UNIX_PORT=/var/lib/mysql_3307/mysql_3307.sock

MYSQL_TCP_PORT=3307

export MYSQL_UNIX_PORT MYSQL_TCP_PORT

 

初始化数据库

mysql_install_db --user=mysql  --datadir=/var/lib/mysql_3307/

 

编辑my.cnf文件

/var/lib/mysql_3307/

[client]
#password       = your_password
port            = 3307
socket          = /var/lib/mysql_3307/mysql_3307.sock

[mysqld]
port            = 3307
socket          = /var/lib/mysql_3307/mysql_3307.sock
datadir         = /var/lib/mysql_3307
skip-locking
key_buffer = 512M
#max_allowed_packet = 4M
max_allowed_packet = 8M
table_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 12M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
back_log = 384
skip-name-resolve
max_connections=500
wait_timeout = 10
log-bin=mysql-bin
server-id       = 2
replicate-ignore-db = test
[mysqldump]
quick
max_allowed_packet = 16M
long_query_time = 3
log-slow-queries = /var/lib/mysql_3307/slow.log
log-queries-not-using-indexes

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

启动mysql

mysqld_safe --defaults-file=/var/lib/mysql_3307/my.cnf &

 

登录mysql

mysql -uroot --socket=/var/lib/mysql_3307/mysql_3307.sock