linux系统下mysql的主从配置教程
程序员文章站
2022-03-30 12:25:38
centos6.5 192.168.1.84 主机
centos6.5 192.168.1.83 从属
一、主服务器配置:
创建同步用户并指定服务器地址
[root@n...
centos6.5 192.168.1.84 主机
centos6.5 192.168.1.83 从属
一、主服务器配置:
创建同步用户并指定服务器地址
[root@node04 ~]# mysql -u root -p111111 mysql>use mysql; mysql>grant replication slave on *.* to 'tongbu'@'192.168.1.84' identified by '123456'; mysql>flush privileges #刷新权限
授权用户tongbu只能从192.168.1.83这个地址访问192.168.1.84上的数据库
修改/etc/my.cnf配置文件
[root@node04 ~]# vim /etc/my.cnf 内容: [mysqld] default-character-set=utf8 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 lower_case_table_names = 1 # master db config 添加的内容 log-bin=MySQL-bin #二进制日志文件 server-id=1 #服务器ID binlog-do-db=test01 #要同步的数据库 binlog-ignore_db=mysql #不同步mysql系统数据库 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] default-character-set=utf8 socket=/var/lib/mysql/mysql.sock [root@node04 ~]# /etc/init.d/mysqld restart
查看主服务器master状态:
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | MySQL-bin.000002 | 106 | test01 | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
创建测试数据库进行测试:
mysql> create database test; mysql> use test; mysql> create table test(id INT NOT NULL AUTO_INCREMENT, name varchar(15) NOT NULL, PRIMARY KEY (id))AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql>insert into test values(1, "aaa"),(2, "bbb"),(3,"ccc"); mysql> select *from test; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ 3 rows in set (0.00 sec)
将这个test数据库的数据备份下来,需要先上锁
mysql> flush tables with read lock; #数据库只读锁命令 unlock table 解锁 [root@node04 ~]# mysqldump -uroot -p111111 test > /root/test.sql #导出数据库结构及数据 [root@node04 ~]# mysqldump -uroot -p111111 -ntd -R test > test_f.sql #
将test.sql发送到slave机器上
二、从服务器配置
[root@node03 ~]#vim /etc/my.cnf [mysqld] default-character-set=utf8 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 lower_case_table_names = 1 #slave db config 添加的内容 server-id=2 #服务器ID,与主服务器一定不相同 log-bin=MySQL-bin #二进制日志文件 binlog-do-db=test01 #同步的数据库 binlog-ignore-db=mysql #不同步mysql系统数据库 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] default-character-set=utf8 socket=/var/lib/mysql/mysql.sock [root@node03 ~]# /etc/init.d/mysqld restart
导入数据库:
[root@node04 ~]# mysqldump -uroot -p111111 test < test.sql
配置主从同步:
[root@node03 ~]# mysql -u root -p111111 mysql> use mysql mysql> stop slave; mysql> change master to master_host='192.168.1.84', master_user='tongbu', master_password='123456', master_log_file='MySQL-bin.000001',#show master status得到的日志文件名 master_log_pos=106; mysql> start slave; mysql> show slave statue\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.84 Master_User: tongbu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MySQL-bin.000002 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 251 Relay_Master_Log_File: MySQL-bin.000002 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: 106 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)
同步成功,进行测试:
从服务器:
mysql> use test mysql> select *from test; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ 3 rows in set (0.00 sec)
主服务器:
mysql> insert into test values(11, "xxx"); mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 11 | xxx | +----+------+ 4 rows in set (0.01 sec)
查看从服务器:
mysql> select *from test; mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 11 | xxx | +----+------+ 4 rows in set (0.01 sec)
成功!!
上一篇: MySql配置文件模板
推荐阅读
-
Linux系统下启动MySQL的命令及相关知识
-
最全的mysql 5.7.13 安装配置方法图文教程(linux) 强烈推荐!
-
CentOS 7系统下配置自定义JDK的教程
-
linux 中的MYSQL命令汇总 适合学习linux下配置mysql的朋友
-
Linux下将Mysql和Apache加入到系统服务里的方法
-
Linux虚拟机下mysql 5.7安装配置方法图文教程
-
CentOS 7系统下配置自定义JDK的教程
-
Linux(Ubuntu)下mysql5.7.17安装配置方法图文教程
-
Linux(Ubuntu)下Mysql5.6.28安装配置方法图文教程
-
Linux虚拟机下mysql 5.7安装配置方法图文教程