实现MySQL的a-b

实现MySQL的a-b

一、需要环境
 
    两台虚理机都安装 MYSQL服务  IP分别为192.168.1.1    192.168.1.2
 
二、以下是个操作步骤；
 
    虚理机一
 
[root@localhost ~]# yum install mysql mysql-server -y
 
[root@localhost ~]# vim /etc/my.cnf
 
[mysqld]
 
datadir=/var/lib/mysql
 
socket=/var/lib/mysql/mysql.sock
 
user=mysql
 
server_id = 1
 
log-bin=binlog
 
log-bin-index=binlog.index
 
# Default to using old password format for compatibility with mysql 3.x
 
# clients (those using the mysqlclient10 compatibility package).
 
old_passwords=1
 
# Disabling symbolic-links is recommended to prevent assorted security risks;
 
# to do so, uncomment this line:
 
# symbolic-links=0
 
[mysqld_safe]
 
log-error=/var/log/mysqld.log
 
pid-file=/var/run/mysqld/mysqld.pid
 
[root@localhost ~]# servicr mysqld start
 
bash: servicr: command not found
 
[root@localhost ~]# service  mysqld start
 
[root@localhost ~]# mysql
 
mysql> quit
 
Bye
 
[root@localhost ~]# mysqldump -A -x >/tmp/full.sql
 
[root@localhost ~]# scp /tmp/full.sql  root@192.168.1.2:/tmp/
 
root@192.168.1.2's password:
 
full.sql                                      100%  412KB 412.4KB/s  00:00   
 
[root@localhost ~]# mysql
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 4
 
Server version: 5.0.77-log Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show master status;
 
+---------------+----------+--------------+------------------+
 
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+---------------+----------+--------------+------------------+
 
| binlog.000003 |      376 |              |                  |
 
+---------------+----------+--------------+------------------+
 
1 row in set (0.00 sec)
 
mysql>                                                                           
 
    虚理机二
 
[root@localhost ~]# yum install mysql  mysql-server -y
 
[root@localhost ~]# vim /etc/my.cnf
 
[mysqld]
 
datadir=/var/lib/mysql
 
socket=/var/lib/mysql/mysql.sock
 
user=mysql
 
server_id = 2
 
relay_log = /var/lib/mysql/mysql-relay-bin
 
relay_log_index=/var/lib/mysql/mysql-relay-bin.index
 
# Default to using old password format for compatibility with mysql 3.x
 
# clients (those using the mysqlclient10 compatibility package).
 
old_passwords=1
 
# Disabling symbolic-links is recommended to prevent assorted security risks;
 
# to do so, uncomment this line:
 
# symbolic-links=0
 
[mysqld_safe]
 
log-error=/var/log/mysqld.log
 
pid-file=/var/run/mysqld/mysqld.pid
 
[root@localhost ~]# service mysqld restar
 
[root@localhost ~]# cd /var/lib/mysql/
 
[root@localhost mysql]# ls
 
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test
 
[root@localhost mysql]# rm -fr *
 
[root@localhost mysql]# ls
 
[root@localhost mysql]# service mysqld restart
 
[root@localhost mysql]# mysql < /tmp/full.sql
 
[root@localhost mysql]# mysql> change master to master_host='192.168.1.1', master_port=3306, master_user='hfj', master_password='123', master_log_file='binlog.000003',master_log_pos=376;
 
[root@localhost mysql]# mysql
 
mysql> change master to master_host='192.168.1.1', master_port=3306, master_user='hfj', master_password='123', master_log_file='binlog.000003',master_log_pos=376;
 
Query OK, 0 rows affected (0.04 sec)
 
mysql> start slave;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slavw status;
 
mysql> show slave status \G;
 
*************************** 1. row ***************************
 
            Slave_IO_State: Waiting for master to send event
 
                Master_Host: 192.168.1.1
 
                Master_User: hfj
 
                Master_Port: 3306
 
              Connect_Retry: 60
 
            Master_Log_File: binlog.000003
 
        Read_Master_Log_Pos: 376
 
            Relay_Log_File: mysql-relay-bin.000002
 
              Relay_Log_Pos: 232
 
      Relay_Master_Log_File: binlog.000003
 
          Slave_IO_Running: Yes
 
          Slave_SQL_Running: Yes
 
            Replicate_Do_DB:
 
        Replicate_Ignore_DB:
 
        Replicate_Do_Table:
 
    Replicate_Ignore_Table:
 
    Replicate_Wild_Do_Table:
 
Replicate_Wild_Ignore_Table:
 
                Last_Errno: 0
 
                Last_Error:
 
              Skip_Counter: 0
 
        Exec_Master_Log_Pos: 376
 
            Relay_Log_Space: 232
 
            Until_Condition: None
 
            Until_Log_File:
 
              Until_Log_Pos: 0
 
        Master_SSL_Allowed: No
 
        Master_SSL_CA_File:
 
        Master_SSL_CA_Path:
 
            Master_SSL_Cert:
 
          Master_SSL_Cipher:
 
            Master_SSL_Key:
 
      Seconds_Behind_Master: 0
 
1 row in set (0.00 sec)
 
ERROR:
 
No query specified
 
mysql>