mysql主從同步執行個體

標籤：mysql   伺服器   local   

mysql本版：mysql-5.5.19.tar.gz  （放在了/root 下）

cmake本版：cmake-3.0.2.tar.gz

主庫192.168.5.204 從庫192.168.5.203

1. 在主伺服器安裝mysql（先安裝cmake，用來編譯mysql）

   [[email protected] ~]# tar zxvf cmake-3.0.2.tar.gz

   [[email protected] ~]# cd cmake-3.0.2

   [[email protected] cmake-3.0.2]# ./bootstrap 

   [[email protected] cmake-3.0.2]# make && make install 

   [[email protected] ~]# tar zxvf mysql-5.5.19.tar.gz 

   [[email protected] ~]# cd mysql-5.5.19

   [[email protected] mysql-5.5.19]#   cmake  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql         -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/usr/local/mysql/etc                 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8                               -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=1         -DENABLED_LOCAL_INFILE=ON -DWITH_DEBUG=0 -DWITH_INNOBASE_STORAGE_ENGINE=1              -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1                   -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DENABLE_DTRACE=0

   [[email protected] mysql-5.5.19]# make && make install

2.添加mysql使用者和組

    [[email protected] ~]# groupadd mysql 

    [[email protected] ~]# useradd -g mysql mysql

3.設定檔

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

    [[email protected] mysql]# mkdir etc

    [[email protected] mysql]# cp /root/mysql-5.5.19/support-files/my-medium.cnf /usr/local/mysql/etc/

#chown  -R mysql:mysql /usr/local/mysql

4.初始化mysql

# cd /usr/local/mysql/scripts/

#./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

4.啟動mysql

    # /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my-medium.cnf &

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4C/B6/wKiom1REYnbz5x9JAAHJaLOeNCs239.jpg" title="1.png" alt="wKiom1REYnbz5x9JAAHJaLOeNCs239.jpg" />650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4C/B6/wKiom1REYomAYCRgAALO-MMcjQc468.bmp" title="2.bmp" alt="wKiom1REYomAYCRgAALO-MMcjQc468.bmp" />添加全域變數

#echo  ‘export PATH=$PATH:/usr/local/mysql/bin’>> /etc/profile

開機自動啟動

# echo   ‘/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my-medium.cnf &‘ >>/etc/rc.local  

5.編輯mysql設定檔my-medium.cnf (開啟binlog日誌，server-id =1)

[[email protected] ~]# vim /usr/local/mysql/etc/my-medium.cnf 

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4C/B6/wKiom1REZPig-CSSAADvFOLFskc161.jpg" title="3.png" alt="wKiom1REZPig-CSSAADvFOLFskc161.jpg" />

重新啟動mysql進程

6.登入mysql

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

mysql> grant replication slave on *.* to [email protected]‘%‘ identified by ‘repl‘;

mysql> flush privileges;

在主程式庫伺服器，查看主記錄檔名稱和主日誌當前POS號

mysql>show master status\G

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4C/B9/wKiom1REgnbSR-7hAACHsuzBpiA483.jpg" title="6.png" alt="wKiom1REgnbSR-7hAACHsuzBpiA483.jpg" />

在從庫設定

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_HOST=‘192.168.5.204‘,MASTER_PORT=3306,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘repl‘ ,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE=‘mysql-bin.000007‘, MASTER_LOG_POS=1482;

mysql>start slave;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4C/BA/wKioL1REg5-grdE8AAFbXD8MH5A164.jpg" title="8.png" alt="wKioL1REg5-grdE8AAFbXD8MH5A164.jpg" />

當 看到兩個YES時，表示同步成功

#建立庫和表測試是否同步

create table test(id int,name char(10));

本文出自 “夢三國” 部落格，謝絕轉載！

mysql主從同步執行個體