华风 发表于 2018-10-10 07:31:52

MySQL 基本主从复制

  +----------------+
  +MySQL主从复制 +
  +------------------+
  主从复制基本原理:
  通过Mysql的某一台主机的数据复制到其他主机。复制过程一个服务器充当主服务器,其余为从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。日志记录发送到从服务器的更新。
  注意:对表的更新必须在主服务器上。
  +------------------------+
  master IP :172.25.38.3
  slave IP :172.25.38.4
  rhel-server-6.5-x86_64
  +-----------------------+
  准备操作
  rpm -e `rpm -qa|grep mysql    ##删除一切之前安装的数据库
  yum install -y mysql-server    ##安装数据库
  rpm -qa |grep mysql      ##安装后,查看已安装的数据库安装包
  mysql-5.1.71-1.el6.x86_64
  mysql-server-5.1.71-1.el6.x86_64
  mysql-libs-5.1.71-1.el6.x86_64
  cd /var/lib/mysql          ##进入该目录,删除一切数据
  rm -fr *
  ----------------------------------
  主master
  vim /etc/my.cnf
  
  datadir=/var/lib/mysql         ##数据库的数据路径
  socket=/var/lib/mysql/mysql.sock    ##数据库启动的脚本
  user=mysql
  symbolic-links=0
  server-id=3                  ##最好写成ip的最后一段
  log-bin=mysql-bin
  binlog-do-db=test
  binlog-ignore-db=mysql
  
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  /etc/init.d/mysqld start               ##开启数据库
  /usr/bin/mysqladmin -uroot -p password 'westos'    #设置新密码
  --------------------------------
  从slave
  vim /etc/my.cnf
  
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  server-id=4
  log-bin=mysql-bin
  
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  /etc/init.d/mysqld start
  /usr/bin/mysqladmin -uroot -p password 'westos'
  --------------------------------
  主和从重启服务
  /etc/init.d/mysqld restart##更改配置后,需要重启数据库
  --------------------------------
  主master授权
  mysql -uroot -pwestos
  mysql> grant replication slave on *.* to ly@'172.25.38.%' identified by 'westos';##授权,给用户ly,ip为172.25.38网段下的所有ip,密码为westos
  Query OK, 0 rows affected (0.00 sec)
  mysql> show master status;##主master信息
  +------------------+----------+--------------+------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------------+----------+--------------+------------------+
  | mysql-bin.000004 |      254 | test         | mysql            |
  +------------------+----------+--------------+------------------+
  1 row in set (0.00 sec)
  ------------------------------
  从slave
  mysql> change master to master_host='172.25.38.3', master_user='ly',master_password='westos';## 主ip设置为172.25.38.3,用户名为ly,密码为westos
  Query OK, 0 rows affected (0.59 sec)
  mysql> start slave;##启动slave
  Query OK, 0 rows affected (0.00 sec)
  mysql> show slave status\G;##查看slave状态信息
  *************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
  Master_Host: 172.25.38.3
  Master_User: ly
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000004
  Read_Master_Log_Pos: 254
  Relay_Log_File: mysqld-relay-bin.000005
  Relay_Log_Pos: 399
  Relay_Master_Log_File: mysql-bin.000004
  Slave_IO_Running: Yes##必须为yes
  Slave_SQL_Running: Yes##必须为yes
  -------------------------------------
  主master测试
  mysql> show databases;##显示当前所有数据库
  +--------------------+
  | Database         |
  +--------------------+
  | information_schema |
  | mysql            |
  | test               |
  +--------------------+
  3 rows in set (0.00 sec)
  mysql> use test;##进入test
  Database changed
  mysql> create table usertb (id int(8) not null,name varchar(15) not null);
  Query OK, 0 rows affected (0.14 sec)##创建名为usertb的表
  mysql> insert into usertb values(01,'liu');##插入信息
  Query OK, 1 row affected (0.00 sec)
  mysql> insert into usertb values(02,'li');
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from usertb;##查看表中的所有信息
  +----+------+
  | id | name |
  +----+------+
  |1 | liu|
  |2 | li   |
  +----+------+
  2 rows in set (0.00 sec)
  ------------------------------
  从slave查看数据库信息如下
  mysql> show databases;
  +--------------------+
  | Database         |
  +--------------------+
  | information_schema |
  | mysql            |
  | test               |
  +--------------------+
  3 rows in set (0.00 sec)
  mysql> use test;
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
  Database changed
  mysql> show tables;
  +----------------+
  | Tables_in_test |
  +----------------+
  | usertb         |
  +----------------+
  1 row in set (0.00 sec)
  mysql> select * from usertb;
  +----+------+
  | id | name |
  +----+------+
  |1 | liu|
  |2 | li   |
  +----+------+
  2 rows in set (0.00 sec)
  -----------------------------------

页: [1]
查看完整版本: MySQL 基本主从复制