mysql数据同步
注意； 可用yum -y install mysql* 安装数据库第一步要启动 /etc/init.d/mysqld restart 或者 service mysqld restart
如上述试验不能完成同步 请查看 iptablesselinux 是否关闭
时间是否同步 如是试验可用 hwclock -s 同步系统和bios时间一致
主服务器ip=192.168.2.170
从服务器ip=192.168.2.183
(主master) 设置ip=192.168.2.170
grant all privileges on *.* to backup@'192.168.2.183'> vim /etc/my.cnf
server-id = 1
log-bin = mysql-bin
保存并退出
重启服务
service mysqld restart如不想退出可用system service mysqld restart命令
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 98 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
（从slave）设置ip192.168.2.183
vim /etc/my.cnf
server-id = 2
log-bin = mysql-bin
stop slave;（停止线程）
change master to master_host='192.168.2.170',master_user='backup',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=98;（master_log_file='mysql-bin.000004',master_log_pos=98;这个会在做得时候不同，要注意变通，这里要用到主服务器上的信息）
start slave;（从启线程）
查看
show slave status;
如显示这些内容则正确
----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File| Read_Master_Log_Pos |> +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.2.170 | backup | 3306 | 60 | mysql-bin.000004 | 276 | mysqld-relay-bin.000002 | 413 | mysql-bin.000004 | Yes | Yes | | | | | | | 0 | | 0 | 276 | 413 | None | | 0 | No | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)
下面做测试
在主服务器上
create database shunying；
显示Query OK, 1 row affected (0.01 sec)
在从服务上查看
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
| shunying |
| test |
+--------------------+
在以上的基础上实现双向备份
在ip=192.168.2.183上操作
grant all privileges on *.* to backup@'192.168.2.170'> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection> Current database: *** NONE ***
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 98 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在192.168.2.170上做操作
stop slave;
change master to master_host='192.168.2.183',master_user='backup',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=98;
start slave;
show slave status;
测试
在ip=192.168.2.183上面
create database ying
Query OK, 1 row affected (0.00 sec)
在ip=192.168.2.170上面查看
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
| shunying |
| test |
| ying |
+--------------------+
6 rows in set (0.00 sec)
页:
[1]