1.MySQL-master:192.168.183.201
mysql-slave:192.168.182.202
使用yum方式安装MySQL,并使用service mysqld start启动MySQL数据库;
使用mysqladmin -uroot -p password 123 修改登录密码,默认为空;
2.mysql-master
mysql> create database temp;
Query OK, 1 row affected (0.00 sec)
mysql> use temp;
Database changed
mysql> create table users(
-> user_name char(16) not null,
-> user_passwd char(48) default "",
-> primary key (user_name));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users values("zhangsan","123");
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| zhangsan | 123 |
+-----------+-------------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> quit;
Bye
[root@mysql-master ~]# cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? y
[root@mysql-master ~]# vi /etc/my.cnf
[root@mysql-master ~]# service mysqld restart
停止 mysqld: [确定]
正在启动 mysqld: [确定]
[root@mysql-master ~]# mysql -uroot -p123
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show master status; (用于配置slave节点)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
//为slave赋予权限
mysql> grant replication slave on *.* to rep@'192.168.183.%' identified by'123';
Query OK, 0 rows affected (0.00 sec)
//锁表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//备份表
[root@mysql-master ~]# mysqldump -u root -p123 --all-databases | gzip > /root/database_`date '+%m-%d-%Y'`.sql.gz
[root@mysql-master ~]# scp database_07-27-2017.sql.gz root@192.168.183.202:/root
3.mysql-slave
root@mysql-slave ~]# cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? y
[root@mysql-slave ~]# vi /etc/my.cnf
[root@mysql-slave ~]# service mysqld restart
停止 mysqld: [确定]
正在启动 mysqld: [确定]
[root@mysql-slave ~]# mysql -uroot -p123
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
[root@mysql-slave ~]# gzip -d database_07-27-2017.sql.gz
[root@mysql-slave ~]# mysql -uroot -p <database_07-27-2017.sql
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| temp |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.183.201', MASTER_USER='rep', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.03 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.183.201
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 253
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 398
Relay_Master_Log_File: mysql-bin.000001
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: 253
Relay_Log_Space: 554
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)
4.测试
mysql-master:
mysql> use temp;
Database changed
mysql> insert into users values("lisi","123");
Query OK, 1 row affected (0.00 sec)
mysql-slave:
mysql> use temp;
Database changed
mysql> select * from users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| zhangsan | 123 |
| lisi | 123 |
+-----------+-------------+
2 rows in set (0.00 sec)