mysql AB复制

[root@station93 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
log-bin=mysql-bin
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[root@station93 mysql]# service mysqld restart
mysql> show master status\G  
************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 314
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
mysql> grant  replication   slave on *.* to slave@192.168.1.17 identified by '0987';
mysql> show grants for slave@192.168.1.17;
root@stu93 ~]#mysqldump --all-database -u root -p0987 > select.sql－－－备份数据库     (导出生产数据库)
[root@stu93 ~]#scp all.sql 192.168.0.16:/root 拷贝到从服务器上  （）
[root@stu93 var]# mysql -uroot -p0987


从配置
[root@station17 ~]# vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-bin=station17_bin
server-id=2
master-host=192.168.1.93
master-user=slave1
master-password=123
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid  


[root@station17 ~]# mysql -uroot -p123 <all.sql 
[root@station17 ~]# mysql -uroot -p123 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.77 Source distribution


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> stop slave
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> change master to master_host='192.168.1.93',master_user='slave',master_log_file='mysql-bin.000001',master_log_pos=660;
Query OK, 0 rows affected (0.01 sec)


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+


[root@station17 ~]# mysql -p123
mysql> show master status\G  
*************************** 1. row ***************************
            File: station17_bin.000001
        Position: 98
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
mysql>  grant  replication   slave on *.* to slave2@192.168.1.5 identified by '123';
mysql> show grants for slave2@192.168.1.5;
[root@station17 ~]# mysqldump --all-database -u root -p123 > select.sql
[root@station17 ~]# scp select.sql 192.168.1.5:/root
select.sql                                    100%  415KB 415.0KB/s   00:00 
                                   
[root@station5 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
server-id=3
master-host=192.168.1.17
master-user=slave2
master-password=123
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@station5 ~]# mysql -uroot -p123 <select.sql
[root@station5 ~]# mysql -p123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


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


mysql> change master to master_host='192.168.1.17',master_user='slave2',master_log_file='station17_bin.000001',master_log_pos=232;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show databases;