mysql主从复制操作步骤

注：、做主从复制的两台服务器server-id不能相同、主从的字符集要一样，不然数据导入会报错、开启db01的log-bin功能[root@db01 mysql]# vim /etc/my.cnflog-bin=mysql-bin[root@db01 ~]# /etc/init.d/mysqld restartShutting down MySQL. SUCCESS!Starting MySQL.. SUCCESS!、查看log_bin是否开启mysql> show variables like "log_bin";+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+ row in set (0.00 sec)、授权用户mysql> grant replication slave on *.* to rep@";mysql> flush privileges;、同步前锁表（不能退出当前的窗口）mysql> create database tang1234;    #创建一个表用于测试主从同步Query OK,  row affected (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || yongzhen888 |+--------------------+ rows in set (0.00 sec)mysql> flush tables with read lock;    #注意不能退出mysql的Shell页面，否则锁表失败、记录位置点mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin. |  | | |+------------------+----------+--------------+------------------+ row in set (0.00 sec)、使用mysqldump进行数据库备份[root@db01 mysql]# mysqldump -uroot -p123456 --events -A -B | gzip >/opt/bak_$(date +%F).sql.gz、解锁数据库（从库完成数据库恢复并开启slave模式）mysql> unlock tables;.恢复数据mysql -uroot -poldboy456 -S /data//mysql.sock </server/backup/rep_bak_2015--.sql.从库开始同步CHANGE MASTER TOMASTER_HOST='172.16.182.5',MASTER_PORT=,MASTER_USER='rep',MASTER_PASSWORD=',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=;CHANGE MASTER TO MASTER_LOG_FILE=;mysql> flush logs;mysql> start slave;mysql> show slave status\G;*************************** . row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.0.0.3Master_User: repMaster_Port:Connect_Retry:Master_Log_File: mysql-bin.Read_Master_Log_Pos:Relay_Log_File: node3-relay-bin.Relay_Log_Pos:Relay_Master_Log_File: mysql-bin.Slave_IO_Running: YesSlave_SQL_Running: Yes