MySQL主从复制功能

需求：

有一个数据库mydb需要做主从复制。

做主从配置能解决两个问题，第一是实时备份，第二是从服务器可以用来处理查询，从而降低主服务器的压力。

一、配置主服务器：

    修改配置文件

    vim /etc/mysql/my.cnf

     在[mysqld]下做如下配置

       log-bin=mysql-bin                                             mysql-bin是二进制文件名
       server-id=1                                                       
       binlog-do-db=mydb                                   
       binlog-ignore-db=mysql,test

     binlog-do-db 和 binlog-ignore-db

这两条叫做master过滤，当然可以不配这个选项，那么所有的数据库的变更都会被发送到从服务器。    

修改配置文件后要重新启动

mysql   service mysql restart

    增加复制用户

     GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to tom@'%'  identified by '123456';

    

    备份数据库

   锁定数据库

   flush tables with read lock

   set global readonly=1

   在另一个窗口执行备份

     mysqldump -uroot -proot --master-data  mydb > ~/mydb_bak.sql

     一定要加上--master_data

     查看master状态,并做记录：

     show master status;

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mysqlmaster.000004 |      375 | lucky_draw   | mysql,test       |
+--------------------+----------+--------------+------------------+

     备份完成后，在第一个窗口执行unlock tables

二、把数据还原到从数据库上

       mysqladmin -uroot -proot create mydb;

       mysql  -uroot -proot mydb < mydb_bak.sql

三、配置从数据库

[mysqld]      
server-id=2

replicate-do-db=mydb

replicate-innore-db=test,mysql

这两行叫做slave过滤器

   配置完重新启动数据库

四、登录从数据库

执行配置：

change master to

> master_host='192.168.30.42',

>master_port=3306,

>master_user='tom',

>master_password='123456',

>master_log_file='mysqlmaster.000004',

>master_log_pos=375;

slave start

五、确认主从正常工作

   在主机上执行 show processlist\G; 如果正常工作应该会输出以下信息：

     Id: 93
     User: tom
     Host: lifeix-l99.local:47427
     db: NULL
    Command: Binlog Dump
    Time: 1817
    State: Has sent all binlog to slave; waiting for binlog to be updated

    Info: NULL

   以上信息表示有一个用同步用户连接上了数据库在读取二进制日志文件

  在从机上执行 show processlist\G;如果正常工作会输出以下信息：

*************************** 1. row ***************************
     Id: 45
     User: system user
     Host:
     db: NULL
    Command: Connect
    Time: 2112
    State: Waiting for master to send event

    Info: NULL

以上信息表示 Slave_IO_Running正在运行，等待主机发送信息

*************************** 2. row ***************************

     Id: 46
     User: system user
     Host:
     db: NULL
     Command: Connect
     Time: 2015
     State: Has read all relay log; waiting for the slave I/O thread to update it
     Info: NULL

以上信息表示 Slave_SQL_Running正在运行，等待更新数据库

执行show slave status\G;

确认两行

            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

六、测试

  在主库上插入一条数据记录，在从库上查看，验证一下同步效果

七、一些知识点

reset slave

reset master

附记：

做该实验时犯的错误，或者尚有疑惑的问题

正确的语句：GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to tom@'%'  identified by '123456';

   这个语句上犯了很多错误：

    最初只给了 REPLICATION SLAVE， 同步不成功

    后来增加了  REPLICATION CLIENT和FILE成功

    to tom@'%' 这个字句写成了 to tom@'*'  这个很容易记混，特此写出来

    曾经写过 to tom@'192.168.30.19' ,执行赋值的时候，主服务器提示了tom@'xxx.local'登录错误， 其中xxx.local是192.168.30.19的主机名

 

在配置主从数据库之前需要确认网络连接正常

   mysql查看服务端口的命令是show variables like 'port' 确认服务端口是 3306

   为了测试顺利进行可以执行service iptables stop

   新安装的mysql数据库 有一个配置选项是  bind-address            = 127.0.0.1  这个修改成本机的Ip, Linux下网络服务使用127.0.0.1好像不能被远程访问，这个需要确认一下，我记的windows下不存在这种情况。

   
mysql配置参数详解:

innodb_buffer_pool_size

       如果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率

innodb_log_file_size
     对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间

innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit  （这个很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据

sync-binlog

当sql单独的 sql语句执行完成以后，或者事物被提交后，二进制日志立刻些些硬盘，这一条是保证二进制日志和数据文件的一致性的，主要是防止mysql发生crash后二进制日志不完整，当然他会造成性能下降。设为1是最安全的选择，因为你最多会丢失一个sql语句或者一个事物的二进制操作数据。开启sync-binlog会导致mysql性能下降。