Mysql主从复制

Tips:Mysql yum安装

yum -y install mysql-server

vim /etc/my.cnf

[mysqld]
character-set-server = utf8

[mysql]
default-character-set = utf8

chkconfig --list mysqld

chkconfig mysqld on

service mysqld start

service mysqld status

Tips:mysql设置root密码

mysql -u root

mysql> use mysql

mysql> select user,host,password from mysql.user; // 查看用户信

mysql> update user set password=password(‘123') where user='root';

mysql> flush privileges;

mysql> quit

Tips:Mysql遗忘密码如何重置

service mysqld stop

mysqld_safe --skip-grant-tables &

mysql -u root

mysql> use mysql

mysql> update user set password=password(‘123') where user='root';

mysql> flush privileges;

mysql> quit

service mysqld restart

Tips: 防火墙打开3306端口

关闭掉selinux  

#/usr/sbin/setenforce 0 立刻关闭 SELINUX

#/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

#/etc/rc.d/init.d/iptables save  

Tips:外部访问3306口

telnet  192.168.134.145  3306

确保主从服务器数据一致，bin-log日志状态都重置

Tips:修改主从配置

修改主服务器master:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=1    //id必须集群唯一

Tips:修改从服务器slave
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=2      

Tips:在主服务器上建立帐户并授权slave

service mysqld restart

/usr/local/mysql/bin/mysql -uroot -p

GRANT REPLICATION SLAVE ON *.* to ‘mysync’@‘%’ identified by ‘123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.134.145，加强安全。

 

Tips:登录主服务器的mysql，查询master的状态

mysql>show master status;

 +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      250 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

 

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

Tips:配置从服务器Slave

mysql>change master to master_host='192.168.134.144',master_user=‘mysync',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=250;   //注意不要断开，“250”无单引号。

   Mysql>start slave;    //启动从服务器复制功能

Tips:检查从服务器复制功能状态

 

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.134.144 //主服务器地址

                  Master_User: mysync //授权帐户名，尽量避免使用root

                  Master_Port: 3306 //数据库端口，部分版本没有此行

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 250 //#同步读取二进制日志的位置，大于等于>=Exec_Master_Log_Pos

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes      //此状态必须YES

            Slave_SQL_Running: Yes    //此状态必须YES

 

Tips:检查从服务器复制功能状态