mysql主从复制

vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended  to prevent assorted security risks

symbolic-links=0

log-bin=mysql-bin

binlog-do-db=pd

server-id=1

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

#service mysqld  restart

#service iptables  stop

mysql> create  user ‘slave_cp‘@‘192.168.124.102‘ IDENTIFIED BY ‘salve_cp_123‘;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT  REPLICATION SLAVE ON *.* TO ‘slave_cp‘@‘192.168.124.102‘ ;

Query OK, 0 rows affected (0.00 sec)

mysql>flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB |  Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      446 | pd           |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql>\q

vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended  to prevent assorted security risks

symbolic-links=0

server-id=2

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

#service mysqld  restart

#service iptables  stop

mysql>  stop slave;

Query  OK, 0 rows affected (0.00 sec)

mysql>  CHANGE MASTER TO MASTER_HOST=‘192.168.124.101‘,MASTER_USER=‘slave_cp‘,MASTER_PASSWORD=‘salve_cp_123‘,MASTER_LOG_FILE=‘mysql-bin.000004‘,MASTER_LOG_POS=446;

Query  OK, 0 rows affected (0.26 sec)

mysql>  start slave;

Query  OK, 0 rows affected (0.00 sec)

mysql>  show slave status \G;

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

               Slave_IO_State: Waiting for  master to send event

                  Master_Host:  192.168.124.101

                  Master_User: slave_cp

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:  mysql-bin.000004

           Read_Master_Log_Pos: 446

               Relay_Log_File:  mysqld-relay-bin.000002

                Relay_Log_Pos: 251

         Relay_Master_Log_File: mysql-bin.000004

             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: 446

              Relay_Log_Space: 407

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>\q

锁表

FLUSH TABLES WITH READ LOCK;

解锁

UNLOCK TABLES;