mysql 主从设置从库只读 mysql主从表怎么设置

一、设置主库

　　1、修改主库my.cnf,主要是设置个不一样的id和logbin

#vim /usr/local/webserver/mysql/my.cnf    
          server-id = 1   
          log-bin= binlog   
          binlog-do-db=iea

　　2、启动主库生效    

#/bin/sh /usr/local/webserver/mysql/bin/mysqld_safe --defaults-file=/usr/local/webserver/mysql/my.cnf &

　　3、登陆主库

#/usr/local/webserver/bin/mysql -u root -p

　　4、赋予从库权限帐号，允许用户在主库上读取日志

mysql> grant all privileges on *.* to 'admin'@'%' identified by '12345678';

　　5、检查创建是否成功

select user,host from mysql.user;

　　6、锁主库表

mysql> flush tables with read lock;

　　7、显示主库信息

　　记录File和Position，从库设置将会用到

mysql> show master status;    
        +------------------+----------+--------------+------------------+    
         | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |   
        +------------------+----------+--------------+------------------+   
         | mysql-bin.000003 1635708   |              |                  |    
         +------------------+----------+--------------+------------------+   
         1 row in set (0.00 sec)

　　8、另开一个终端登陆124，打包主库迁移数据

# cd /usr/local/webserver/mysql/
 　　# tar zcvf data124.tar.gz ./data

   二、设置从库

1、传输拿到主库包、解包
        #cd /usr/local/webserver/mysql/   
        #tar zxvf data124.tar.gz   
        2、解锁主库表
        mysql> unlock tables;
        3、修改从库my.cnf
        # vi my.cnf     
        # slave    
        server-id=2   
        master-host=192.168.4.191    
        master-user=admin   
        master-password=12345678 
        4、验证连接主库
        # /usr/local/webserver/mysql/bin/mysql -h 192.168.4.191 -u admin -p 
        5、在从库上设置同步
        设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position       
        mysql> slave stop;   
        mysql> change master to master_host='192.168.4.191',master_user='admin', master_password='12345678',    
                 > master_log_file='binlog.000003', master_log_pos=1635708;    
        mysql> slave start;  
        6、启动从库服务
        mysql> slave start;
        7、进行测试
        在主库上的iea表上建立名为yuhongchun的表
        mysql> CREATE TABLE `yuhongchun` (    `id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,    `username` VARCHAR( 20 ) NOT NULL ,    `password` CHAR( 32 ) NOT NULL ,    `time` DATETIME NOT NULL ,    `number` FLOAT( 10 ) NOT NULL ,    `content` TEXT NOT NULL ,    PRIMARY KEY ( `id` )     ) ENGINE = MYISAM ;  
         在从表中马上看到了效果，主从同步成功了;为了更进一步验证在从库上输入show slave status\G;
         mysql> show slave status\G;
         Slave_IO_Running: Yes(网络正常);Slave_SQL_Running: Yes(表结构正常)，进一步验证了以上过程的正确性，截图如下(一定要保证这二项参数为YES)
 　　在主MySQL上可输入mysql> show full processlist;观察其状态，正确结果也应该如截图所示：

　　三、如果主MySQL发生错误，如何做主从切换呢?

　　1、保证所有从数据库都已经执行了relay log中的全部更新，在从服务器中执行stop slave io_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成.

  

mysql> stop slave io_thread;    
         Query OK, 0 rows affected (0.01 sec)    
          mysql> show processlist;    
         +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+    
         | Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |    
         +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+    
          |  5 | system user |           | NULL | Connect | -626 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |    
          |  6 | root        | localhost | iea  | Query   |    0 | NULL                                                                  | show processlist |    
          +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

            2 rows in set (0.00 sec)  

　　2、在从服务器上执行stop slave，reset master命令，重置成主数据库

mysql>stop slave;      
          Query OK,0 affected (0.00 sec)      
          mysql>reset master;      
          Query OK,0 affected (0.00 sec)

　　3、删除新的主服务器数据库目录中的master.info和文件，否则下次重启时还会按照从服务器来启动. 

MySQL主从架构投入生产前后应该注意的事项：

　　一、配置前，master和slave的hostname一定要取个不同的，免得配置时发生问题;另外，强烈建议ntpdate二台服务器的时间，不然来个未来(feature)时间就麻烦了。

　　二、由于MySQL数据库走的都是内网，所以二台机器的iptables可以关闭，在配置过程中由于没关iptables发生了错误，直接导致admin在slave数据库上连不上主数据库，这个特指出来给大家借荐 下;

　　三、主MySQL的binlog功能一定要打开，我们的线上服务器有次由于PHP程序误操作，发生了改单错误，幸亏用binlog恢复过来了;但开启此功能要注意binlog的大小，有次Nagios狂报警，binlog日志都快1T了;

　　四、如果slave服务器同步时出现以下报错：

　　The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

　　说明方从服务器里my.cnf中的server-id有相同的。解决办法:修改my.cnf里的server-id，并重启数据库服务。

　　五、本着防患于未然，如果做主MySQL的备份时，请尽快用shell脚本同时做下FTP的备份工作，即将本地备份数据即时FTP到存储服务器上，事实证明：这样能将备份是救命的稻草的宗旨执行得更为彻底。