mysql如何进行主从备份？

可以参考这里：基于mysqldump快速搭建从库

mysql主从搭建总的来说大致分为3个步骤，一是为主从实例添加复制所需参数以及创建复制用的账户，二在是需要在主库建立快照，三是在从库上添加指向主库IP，端口，用户名，密码，binlog位置等。而对于主从搭建的快照方式有很多种，如使用InnoDB hotbak，xtrabackup，mysqldump以及直接使用tar方式来建立快照。本文主要介绍使用mysqldump方式来建立快照，适用于不超过20GB左右的数据库。

    与本文有关的相关参考：
        使用mysqldump导出数据库
        MySQL 复制简要描述及示例
        MySQL多实例配置(一)
        MySQL多实例配置(二)

1、实例级别的主从搭建

    -- 演示环境，另，本文演示基于同一主机的多实例，主端口为3406，从端口为3506  
    master@localhost[(none)]> show variables like 'version';  
    +---------------+------------+  
    | Variable_name | Value      |  
    +---------------+------------+  
    | version       | 5.6.12-log |  
    +---------------+------------+  
      
    master@localhost[(none)]> system cat /etc/issue  
    CentOS release 5.4 (Final)  
    Kernel \r on an \m  
      
    --有关参主从数配置，请参考MySQL 复制简要描述及示例  
    --创建用于复制的账户  
    master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx';  
    Query OK, 0 rows affected (0.01 sec)  
      
    --全局读锁  
    master@localhost[(none)]> flush tables with read lock;  
    Query OK, 0 rows affected (0.02 sec)  
      
    master@localhost[(none)]> system pwd  
    /data/inst3406  
      
    --获取master binlog位置  
    master@localhost[(none)]> show master status;  
    +--------------------+----------+--------------+------------------+-------------------+  
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
    +--------------------+----------+--------------+------------------+-------------------+  
    | inst3406bin.000001 |     2169 |              |                  |                   |  
    +--------------------+----------+--------------+------------------+-------------------+  
    1 row in set (0.00 sec)  
      
    --使用mysqldump导出实例  
    master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql  
      
    master@localhost[(none)]> system ls  
    alldb.sql  data3406   
      
    --解锁  
    master@localhost[(none)]> unlock tables;  
      
    master@localhost[(none)]> exit  
      
    --从库上面导入dump  
    [mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock       
    --从库上设置主库的相关信息(host,port等等)  
    [mysql@app inst3506]$ mysqls  
    slave@localhost[(none)]> change master to  
        -> MASTER_HOST='192.168.1.177',   
        -> MASTER_USER='repl',   
        -> MASTER_PASSWORD='xxx',   
        -> MASTER_PORT=3406,   
        -> MASTER_LOG_FILE='inst3406bin.000001',   
        -> MASTER_LOG_POS=2169;   
    Query OK, 0 rows affected, 2 warnings (0.01 sec)  
      
    --启动slave  
    slave@localhost[(none)]> start slave;  

2、部分库从库搭建

    --以下演示为仅搭建部分从库，为只同步sakila tempdb 2个库  
    --重置从库  
    slave@localhost[(none)]> stop slave;  
    Query OK, 0 rows affected (0.01 sec)  
      
    slave@localhost[(none)]> reset slave all;  
    Query OK, 0 rows affected (0.01 sec)  
      
    --为从库添加只同步sakila tempdb 2个库，以下为修改后的结果  
    [mysql@app ~]$ grep replicate /data/inst3506/data3506/my3506.cnf   
    replicate-do-db=test  
    replicate-do-db=sakila  
      
    [mysql@app ~]$ grep skip-slave /data/inst3506/data3506/my3506.cnf  
    skip-slave-start  
      
    --修改后重启3506以使从配置生效  
    [mysql@app ~]$ mysqladmin -uroot -pxxx -S /tmp/mysql3506.sock shutdown  
      
    [mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3506/data3506/my3506.cnf &  
      
    --从主库仅导出sakila tempdb  
    [mysql@app ~]$ mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --single-transaction --master-data=2 -R --database sakila tempdb>multidb.sql  
      
    --在从库端登陆执行dump文件  
    [mysql@app ~]$ mysqls  
    slave@localhost[(none)]> source multidb.sql  
      
    --查看dump期间的master binlog位置  
    slave@localhost[tempdb]> system grep -i "change master" multidb.sql  
    -- CHANGE MASTER TO MASTER_LOG_FILE='inst3406bin.000001', MASTER_LOG_POS=3293117;  
      
    --从库上设置主库的相关信息(host,port等等)  
    slave@localhost[tempdb]> change master to  
        -> MASTER_HOST='192.168.1.177',   
        -> MASTER_USER='repl',   
        -> MASTER_PASSWORD='xxx',   
        -> MASTER_PORT=3406,   
        -> MASTER_LOG_FILE='inst3406bin.000001',   
        -> MASTER_LOG_POS=3293117;   
    Query OK, 0 rows affected, 2 warnings (0.01 sec)  
      
    --启动从库  
    slave@localhost[tempdb]> start slave;  
    Query OK, 0 rows affected (0.01 sec)  
      
    -- Author : Leshami  
    -- Blog   : http://blog.csdn.net/leshami  
    --校验结果  
    slave@localhost[tempdb]> show slave status \G  
    *************************** 1. row ***************************  
                   Slave_IO_State: Waiting for master to send event  
                      Master_Host: 192.168.1.177  
                      Master_User: repl  
                      Master_Port: 3406  
                    Connect_Retry: 60  
                  Master_Log_File: inst3406bin.000001  
              Read_Master_Log_Pos: 3293117  
                   Relay_Log_File: relay-bin.000002  
                    Relay_Log_Pos: 285  
            Relay_Master_Log_File: inst3406bin.000001  
                 Slave_IO_Running: Yes  
                Slave_SQL_Running: Yes  
                  Replicate_Do_DB: test,sakila  
      
    --需要注意的是，本文的mysqldump期间，--single-transaction 仅仅对innodb引擎有效  
    --如果仅使用--master-data，则会开启--lock-all-tables  
    The --master-data and --single-transaction options can be used simultaneously, which provides a convenient way to  
           make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB  
           storage engine.  

库大的话还是用热备方式来解决了，这个mysqldump有点鸡肋。使用Innobackupex快速搭建(修复)MySQL主从架构

MySQL的主从搭建大家有很多种方式，传统的mysqldump方式是很多人的选择之一。但对于较大的数据库则该方式并非理想的选择。使用Xtrabackup可以快速轻松的构建或修复mysql主从架构。本文描述了使用innobackupex快速来搭建或修复主从架构。供大家参考。

    1、基于主库做一个完整备份  
    # mkdir -p /log/bakforslave  
    # innobackupex --user=root -password=*** --socket=/tmp/mysql.sock \  
    --defaults-file=/etc/my.cnf /log/bakforslave --parallel=3 --safe-slave-backup --no-timestamp   
      
    2、复制数据库到备机  
    # tar -czvf bakforslave.tar.gz ./bakforslave/  
    # scp bakforslave.tar.gz [email]robin@172.16.10.51[/email]:~  
    # scp /etc/my.cnf [email]robin@172.16.10.51[/email]:~/mymaster.cnf  
      
    3、在备机上恢复  
    ###备机解压打包的备份文件  
    # mv /home/robin/bakforslave.tar.gz /data  
    # cd /data  
    # tar -xvf bakforslave.tar.gz  
      
    ### prepare 备份  
    # innobackupex --user=root -password=*** --socket=/tmp/mysql.sock --defaults-file=/home/robin/mymaster.cnf \  
     --apply-log --use-memory=4GB /data/bakforslave  
      
    ###如果是修复从库，从库为启动状态应先停止从库，再做如下操作，否则可以跳过   
    # service mysqld stop   
      
    ###还原备份的数据文件  
    # mv mysqldata mysqldatabk  
    # mv bakforslave mysqldata  
    # chown -R mysql:mysql mysqldata  
      
    ###如果是新搭建的从库，此时可以修改主库的my.cnf为本机的my.cnf，  
    ###如果为修复，则可以直接使用原有的配置文件或根据需要修改。  
    # cp /home/robin/mymaster.cnf /etc/my.cnf  
    # vi /etc/my.cnf         ###此处应修改使用一个不同的server_id，同时可以根据需要修改相关路径及端口配置等。  
    # service mysqld start   ###修改完毕后可以启动mysqld  
      
    4、主库授权用于复制的用户  
    mysql> grant replication slave,replication client on *.* to repl2@'172.16.10.%' identified by '***';  
      
    ### 验证shell 提示符下登陆到主库  
    # mysql -urepl2 -p -h172.16.10.88  
      
    5、启动slave  
    # more /data/mysqldata/xtrabackup_binlog_info  
    mysql-bin.000136        73752825  
      
    mysql> CHANGE MASTER TO  
    MASTER_HOST='172.16.10.88',            --Author: Leshami  
    MASTER_USER='repl2',                   --Blog  : http://blog.csdn.net/leshami  
    MASTER_PASSWORD='***',  
    MASTER_LOG_FILE='mysql-bin.000136',  
    MASTER_LOG_POS=73752825;  
      
    mysql> start slave;  
      
    6、验证结果  
    mysql> show slave status \G  
    *************************** 1. row ***************************  
                   Slave_IO_State: Waiting for master to send event  
                      Master_Host: 172.16.10.88  
                      Master_User: repl2  
                      Master_Port: 3306  
                    Connect_Retry: 60  
                  Master_Log_File: mysql-bin.000136  
              Read_Master_Log_Pos: 96592981  
                   Relay_Log_File: mysqld-relay-bin.000002  
                    Relay_Log_Pos: 72113  
            Relay_Master_Log_File: mysql-bin.000136  
                 Slave_IO_Running: Yes  
                Slave_SQL_Running: Yes  
                  Replicate_Do_DB: test,bs_com,bs_sysmsg,bs_bak  
              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: 73824655  
                  Relay_Log_Space: 22840613  
                  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: 3815  
    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: 2  
                      Master_UUID: afd6bca4-6636-11e3-9d60-74867ae1c47c  
                 Master_Info_File: /data/mysqldata/master.info  
                        SQL_Delay: 0  
              SQL_Remaining_Delay: NULL  
          Slave_SQL_Running_State: updating  
               Master_Retry_Count: 86400  
                      Master_Bind:   
          Last_IO_Error_Timestamp:   
         Last_SQL_Error_Timestamp:   
                   Master_SSL_Crl:   
               Master_SSL_Crlpath:   
               Retrieved_Gtid_Set:   
                Executed_Gtid_Set:   
                    Auto_Position: 0  
    1 row in set (0.00 sec)收起

楼上回答的很详细，赞:lol收起

--lock-tables 会在备份中依次锁住架构下的所有表，这就是不能写入的原因。对于 InnoDB 引擎的数据库，没有必要使用此参数。建议把 --lock-tables 改成 --single-transaction。收起