注意本次主从服务器上面均一 yum 安装 mysql
主从安装 mysql
- shell> yum -y install mysql mysql-server
- /etc/init.d/mysqld start #初始化数据库, 并启动数据库
主服务器上面
添加以下配置
- server-id=1
- log-bin=mysql-bin #这个一定得设置, 否则没有日志的话, 从数据库上会报错
- [root@localhost etc]# service mysqld stop
- Stopping mysqld: [ OK ]
- [root@localhost etc]# service mysqld start
- Starting mysqld: [ OK ]
- [root@localhost etc]# service mysqld status
mysqld (pid 3129) is running...
创建主从复制的帐号:
- mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.137.197' IDENTIFIED BY '123456';
- Query OK, 0 rows affected (0.00 sec)
如果该命令无法执行, 请检查是否用了圆角的', 需要用半角的', 再不行, 那就是 mysql 数据库安装不成功.
192.168.137.197 是从库的地址
backup 是需要同步的用户名 (有些博客说是数据库名称, 坑人不倦)
123456 为远程同步密码
解锁表
- mysql> UNLOCK TABLES;
- Query OK, 0 rows affected (0.00 sec)
正确的状态如下:
- mysql> SHOW MASTER STATUS;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000001 | 106 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
配置错误的情况, 解决办法见文章最后的疑难解答.
- mysql> SHOW MASTER STATUS;
- Empty set (0.00 sec)
修改从库配置:
[root@localhost ~]# vi /etc/my.cnf
添加语句
- log-bin = mysql-bin
- server_id = 2
- master-host = 192.168.137.33
- master-user = backup
- master-pass = 123456
- master-port = 3306
- master-connect-retry = 60
重启 mysql /etc/init.d/mysqld restart
- mysql> CHANGE MASTER TO
- MASTER_HOST='192.168.137.33',
- MASTER_USER='backup',
- MASTER_PASSWORD='123456',
- MASTER_PORT=3306,
- MASTER_LOG_FILE='mysql-bin.000001',
- MASTER_LOG_POS=106,
- MASTER_CONNECT_RETRY=10;
- Query OK, 0 rows affected (0.02 sec)
测试主从是否配置成功.
- mysql> show slave status\G;
- # 注意在 slave 上面检测 show slave status 时要保证 1, Slave_IO_State 和 Slave_IO_Running 都是有状态的才算 salve 进程启动, 后续才能进行主从操作. 切记!
测试: 新建表:
- CREATE TABLE Persons
- -> (
- -> Id_P int,
- -> LastName varchar(255),
- -> FirstName varchar(255),
- -> Address varchar(255),
- -> City varchar(255)
- -> );
- mysql> desc Persons;
- +-----------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------+--------------+------+-----+---------+-------+
- | Id_P | int(11) | YES | | NULL | |
- | LastName | varchar(255) | YES | | NULL | |
- | FirstName | varchar(255) | YES | | NULL | |
- | Address | varchar(255) | YES | | NULL | |
- | City | varchar(255) | YES | | NULL | |
- +-----------+--------------+------+-----+---------+-------+
- 5 rows in set (0.01 sec)
从服务器上面 mysql>desc Persons 之后出现上述一样的结果则说明配置, 主从同步成功. 但是这个只是主服务器的文件变动之后同步到从服务器上面去了, 从服务器的 sql 语句执行, 没有影响到主服务器的变化, 并没有实现真正意义上的主从同步. 请知悉!
来源: http://www.bubuko.com/infodetail-2641375.html