mysql主從配置

標籤：mysql主從配置

mysql主從配置

 

replication主從，使兩個資料庫資料保持一致

主A資料變化，產生日誌，傳送給從B，B根據日誌做相應變化，使資料與A一致。從而保持A、B資料庫保持一致

 

 

 

現實中，資料庫主從在兩台裝置上做，現為方便和節省資源，我就在同一台上做了，在兩台上做，配置類似

[[email protected] ~]# ls /usr/local/

bin/    games/   lib/     sbin/   src/

etc/    include/ libexec/ share/

[[email protected] ~]# rm -rf /usr/local/mysql             //如果安裝過資料庫，先刪除原先的

[[email protected] ~]# cd /usr/local/src/

[[email protected] src]# ls

mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

[[email protected] src]# tar zxvfmysql-5.1.40-linux-i686-icc-glibc23.tar.gz

[[email protected] src]# mvmysql-5.1.40-linux-i686-icc-glibc23 /usr/local/mysql

[[email protected] src]# grep "mysql"/etc/passwd

[[email protected] src]# useradd -s /sbin/nologinmysql

[[email protected] src]# cd /usr/local/mysql/

[[email protected] mysql]# ls

[[email protected] mysql]# cpsupport-files/my-small.cnf /etc/my.cnf

cp: overwrite `/etc/my.cnf‘? y

[[email protected] mysql]# vim /etc/my.cnf

[[email protected] mysql]# cpsupport-files/mysql.server /etc/init.d/mysqld

[[email protected] mysql]# vim  /etc/init.d/mysqld

basedir=/usr/local/mysql

datadir=/data/mysql

 

 

[[email protected] mysql]# rm -rf /data/mysql             //如果之前安裝過，存在就刪除

[[email protected] mysql]#./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

[[email protected] mysql]# /etc/init.d/mysqldstart

[[email protected] mysql]# ps aux |grep mysql

root     3694  0.0  0.1  5060  1344 pts/2    S   21:02   0:00 /bin/sh/usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql--pid-file=/data/mysql/Client.pid

mysql    3793  0.9  1.2 102904 13380 pts/2    Sl  21:02   0:00/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql--user=mysql --log-error=/data/mysql/Client.err--pid-file=/data/mysql/Client.pid --socket=/tmp/mysql.sock --port=3306

 

[[email protected] mysql]# netstat -lnp |grep3306

tcp       0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3793/mysqld

//主mysql安裝成功，並啟動了

 

 

現安裝第二個mysql,從mysql。

[[email protected] mysql]# cd ..

[[email protected] local]# ls

[[email protected] local]# cp -r mysqlmysql_slave

[[email protected] local]# cd mysql_slave/

[[email protected] mysql_slave]# cp /etc/my.cnf .              //設定檔拷貝到目前的目錄

[[email protected] mysql_slave]# pwd

/usr/local/mysql_slave

[[email protected] mysql_slave]# vim my.cnf

[mysqld]

port            = 3307

socket          = /tmp/mysql_slave.sock

datadir         = /data/mysql_slave

 

[[email protected] mysql_slave]#./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave

[[email protected] mysql_slave]# ls/data/mysql_slave/

mysql test

[[email protected] mysql_slave]# cd /etc/init.d/

[[email protected] init.d]# cp mysqld mysqldslave

basedir=/usr/local/mysql_slave

datadir=/data/mysql_slave

conf=/etc/my.cnf 改成conf=$basedir/my.cnf

[[email protected] init.d]#/etc/init.d/mysqldslave start

[[email protected] init.d]# ps aux |grep mysql

[[email protected] init.d]# netstat -lnp |grepmysql

tcp    0   0 0.0.0.0:3306          0.0.0.0:*            LISTEN      3793/mysqld

tcp    0    0 0.0.0.0:3307        0.0.0.0:*             LISTEN     4105/mysqld

//兩個mysql都已啟動

 

 

 

 

現在配置主從，主3306，從3307

[[email protected] init.d]# cd

[[email protected] ~]# mysql                      //如無該命令可用/usr/local/mysql/bin/mysql

-bash: mysql: command not found

[[email protected] ~]# /usr/local/mysql/bin/mysql

mysql> quit

Bye

[[email protected] ~]# vim /etc/profile.d/path

export PATH=$PATH:/usr/local/mysql/bin

[[email protected] ~]# source !$

source /etc/profile.d/path

 

[[email protected] ~]# mysql                         //預設登入3306的

mysql> quit

[[email protected] ~]# mysql -S /tmp/mysql.sock         //登入3306的

mysql> quit

[[email protected] ~]# mysql -S /tmp/mysql_slave.sock       //登入3307的

mysql> quit

[[email protected] ~]# mysql -h127.0.0.1 -P3307            //登入指定監聽連接埠的

 

[[email protected] ~]# mysql                //登入主的

mysql> create database db1;            //建立一個表

mysql> quit

[[email protected] ~]# mysqldump -S/tmp/mysql.sock mysql>123.sql     //拷貝一個庫到檔案

[[email protected] ~]# vim 123.sql                                 //查看

[[email protected] ~]# mysql -S /tmp/mysql.sockdb1 <123.sql          //將檔案拷貝到庫

[[email protected] ~]# mysql

mysql> use db1;

mysql> show tables;                                       //庫已拷貝

mysql> quit

 

[[email protected] ~]# vim /etc/my.cnf

server-id       = 1                           //確保與從上server-id不同

log-bin=wang                                //自訂log-bin

#binlog-do-db=db1,db2               //只針對db1,db2做主從（去掉注釋生效）

#binlog-ignore-db=mysql              //無需做主從的庫（去掉注釋生效）

 

[[email protected] ~]# /etc/init.d/mysqld restart

[[email protected] ~]# ls /data/mysql

wang.index wang.000001      // log-bin檔案

[[email protected] ~]# mysql

mysql> grant replication slave on *.* to‘repl‘@‘127.0.0.1‘ identified by ‘123123‘;       

 //設定許可權

mysql> flush privileges;                //重新整理

mysql> flush tables with read lock;       //鎖死，讀

mysql> show master status;            //查看

+-------------+----------+--------------+------------------+

| File        | Position | Binlog_Do_DB |Binlog_Ignore_DB |

+-------------+----------+--------------+------------------+

| wang.000001|      315|              |                  |

+-------------+----------+--------------+------------------+

從上：

[[email protected] ~]# vim /usr/local/mysql_slave/my.cnf

server-id       = 111                   //設定成與主不同

[[email protected] ~]# ls

123.sql

[[email protected] ~]# mysql -S/tmp/mysql_slave.sock -e "create database db1";

//從上建立庫db1

[[email protected] ~]# mysql -S/tmp/mysql_slave.sock db1 <123.sql;

//匯入庫

[[email protected] ~]# mysql -S/tmp/mysql_slave.sock      //登入從

mysql> slave stop;

mysql> change master to master_host=‘127.0.0.1‘,master_port=3306, master_user=‘repl‘, master_password=‘123123‘,master_log_file=‘wang.000001‘, master_log_pos=315;

 

// master_log_file=‘wang.000001‘,master_log_pos=315;值為mysql> show master status;中看到的

 

mysql> slave start;

mysql> show slave status\G;

Slave_IO_Running: No

Slave_SQL_Running: Yes

//兩項值都為Yes表示配置成功，現配置還未正確

 

mysql> slave stop;

[[email protected] ~]# /etc/init.d/mysqldslaverestart

[[email protected] ~]# mysql -S/tmp/mysql_slave.sock

mysql> slave start;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

//兩項都為yes.配置成功了

 

 

驗證測試：

主上

[[email protected] ~]# mysql

mysql> unlock tables;

mysql> use db1;

mysql> show tables;

help_category

mysql> drop table help_category;          //刪除該表

 

 

 

[[email protected] ~]# mysql -S/tmp/mysql_slave.sock

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql> use db1;

mysql> show tables;

無help_category表，主上刪除後，從上同步

 

注意：主從機制不能在從上做操作

 

 

主上：

mysql> drop database db1;

mysql> show database;

 

從上：

mysql> show databases;

db1也被刪除

 

 

實際現實中，主從很容易出現故障，可監控兩個yes的值，判讀主從是否正常

 

本文出自 “Linux學習筆記” 部落格，請務必保留此出處http://9656134.blog.51cto.com/9646134/1678114

mysql主從配置