[原创]CentOS下Mysql双机互为备份

时间:2022-09-22 10:22:54

一、环境:

       1.安装Centos-6.5-x64位系统的机器两台:

    host1:192.168.2.3

    host2:192.168.2.4  (互相能ping通)

       2.安装Mysql。 命令:Yum install mysql-*

二、配置:

       1、启动mysql。命令:service mysqld start

    设置mysql的root密码。命令:mysqladmin –u root password 回车后输入要设置的密码即可。

       2、创建数据库test1和表test1。

              Create database test1;

              Use test1;

              Create table test1(id int(5),name varchar(10));

       3.配置mysql配置文件:/etc/my.cnf.配置内容如下:

       Host1:

              [mysqld]

                     Server-id=1

                     Log-bin=bin-log

                     Binlog-ignore-db=mysql,test,infromation_schema

                     Binlog-do-db=test1

                     Master-host=192.168.2.4                    

        Master-port=3306

                     Master-user=root

                     Master-password=111

                     Master-retry-count=999

                     Master-connect-retry=60

Host2:

              [mysqld]

                     Server-id=2

                     Log-bin=bin-log

                     Binlog-ignore-db=mysql,test,infromation_schema

                     Binlog-do-db=test1

                     Master-host=192.168.2.3

                     Master-port=3306

                     Master-user=root

                     Master-password=110

                     Master-retry-count=999

                     Master-connect-retry=60

       4.重启mysql。命令:service mysqld restart

1.如果不能启动服务,则仔细检查配置文件是否写错,检查无误后关闭防火墙。命令:service iptables stop(chkconfig iptables off)。

2.Host1使用mysql –h host2.IP –u root –p 登录host2的mysql看是否成功

3.Host2使用mysql –h host1.IP –u root –p 登录host1的mysql看是否成功

       成功说明双机上的mysql服务和访问正常。

       5.配置授权。

Host1:        mysql>grant replivation slave on *.* to ‘root’@‘192.168.2.4’ identified by ‘host1中mysql的root密码’;

Host2:        mysql>grant replivation slave on *.* to ‘root’@‘192.168.2.3’ identified by ‘host2中mysql的root密码’;

 

       6.host1host2分别登录本机mysqlMysql –u root –p 回车输入密码。

              查看master和slave状态:

              命令:show master status\G;

                            Show slave status\G;

    Host1:

       [原创]CentOS下Mysql双机互为备份

    Host2:

       [原创]CentOS下Mysql双机互为备份

结果如上图所示,slave_IO_Running和slave_SQLRunning都为YES则表示配置成功。

如果出现slave_IO_Running:NO; Last_IO_Error: Got fatal error 1236 from master when  reading data from binary log:'Client requested master to start  replication from impossible position'

的错误,则是因为主机日志文件的配置问题。

解决:使用show master status\G;查看master的file和position的值,然后用一下命令设置:
mysql> stop slave;
mysql> change master to master_log_file='file值',master_log_pos=position值;
mysql> start slave;

7.测试:

分别在host1和host2的mysql-jhk-test1表中插入不同数据来测试。

      [原创]CentOS下Mysql双机互为备份

结果显示双机实现互为热备份。删除数据和表测试(略)