+ + (3)**USA--EN"--* MySQL 5.1.71 database synchronization * (Replication) - ** master-slave mode ** (CentOS 6.5 x86_64) ~^
+ + (3) **Macau)FDZ" - *PT "--* MySQL 5.1.71 banco de dados de sincronização * (replicação) - ** modo mestre-escravo ** (CentOS 6.5 x86_64) ~^
+ + (3)**KO**--"MySQL은 5.1.71 데이터베이스 동기화 "(복제) - *마스터 - *슬레이브 모드* (CentOS에 6.5 x86_64의)~^----*
16.1.1. How to Set Up Replication
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
server1 : CentOS 6.5 x86_64 , IP: 192.168.128.101
server2 : CentOS 6.5 x86_64 , IP: 192.168.128.102
CentOS 6.5 的 MySQL 是 5.1.71 版
[root@centos1 ~]# yum list | grep mysql-server
mysql-server.x86_64 5.1.71-1.el6 base
[root@centos1 ~]#
----------------------------------------------------------------------------
先在兩台上都安裝啟動 MySQL，暫時先關閉防火牆
yum -y install mysql mysql-server
service mysqld start
chkconfig mysqld on
# 替 MySQL 帳號 root 設定密碼為 123456 (依個人喜好)
/usr/bin/mysqladmin -u root password '123456'
service iptables stop
----------------------------------------------------------------------------
Server1 上
1. 建立同步用帳號和要同步的資料庫，讓另一台主機192.168.128.102上的root帳號在這台上有REPLICATION SLAVE權限
[root@server1 ~]#mysql
mysql>CREATE USER 'root'@'192.168.128.102' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
或
mysql> GRANT FILE ON *.* TO 'root'@'192.168.128.102';
或
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.128.102';
讓權限設定生效(或重新啟動MySQL也可)
mysql> FLUSH PRIVILEGES;
建立新的空資料庫(稱為backup)
mysql> create database backup;
mysql> \q
2. 修改 /etc/my.cnf，在[mysqld]中增加下面資訊
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=backup
#定期去192.168.128.102抓資料
master-host=192.168.128.102
master-user=root
master-password=
master-port=3306
master-connect-retry=60
replicate-do-db=backup
2.重新啟動資料庫讓my.cnf生效
[root@server1 ~]# service mysqld restart
PS : 因為 Server2 尚未設定，所以/var/log/mysqld.log可能有錯誤
等 Server2 設定完，重新啟動後，可以再重新啟動一次Server1
----------------------------------------------------------------------------
Server2 上
1. 建立同步用帳號和要同步的資料庫，讓另一台主機192.168.128.101上的root帳號在這台上有REPLICATION SLAVE權限
[root@server1 ~]# mysql
mysql> CREATE USER 'root'@'192.168.128.101' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.101';
Query OK, 0 rows affected (0.00 sec)
2. 修改 /etc/my.cnf，在[mysqld]中增加下面資訊
(注意，log-bin的設定值不可和Server1上相同)
[mysqld]
server-id=2
log-bin=mysql-bin2
binlog-do-db=backup
#定期去192.168.128.101抓資料
master-host=192.168.128.101
master-user=root
master-password=
master-port=3306
master-connect-retry=60
replicate-do-db=backup
2.重新啟動資料庫
[root@server2 ~]# service mysqld restart
Server1 的再啟動一次
[root@server1 ~]# service mysqld restart
----------------------------------------------------------------------------
可以在兩台機器中任何一台的 backup 資料庫中新增Table或資料，另外一台應該也會出現。
如果失敗
請把Server1上mysql-bin.* 和 mysqld-relay-bin.* 全砍
請把Server2上mysql-bin2.* 和 mysqld-relay-bin.* 全砍
rsync -rvlHpogDtS --delete -e ssh /var/lib/mysql/. root@192.168.128.102:/var/lib/mysql/.
重新建立帳號、權限測試
-----------------------------------------------------------------------------------------------------------------------
++*2).*.
[] 如何指令--"每天自動備份 MySQL "-方法 ~
++*2).*.
[] 如何指令--"每天自動備份 MySQL "-方法 ~
詳見如下--]
每天自動備份 MySQL 方法
用 vi 編輯 /root/MySQLDump.sh 和 /etc/crontab , 內容如下
[root@localhost ~]# cat /root/MySQLDump.sh
cd /var/lib/mysql
mysqlcheck -a -c -o -r -uroot -p密碼 -h localhost 資料庫名稱
mysqldump forum -uroot -p密碼 -h localhost > 資料庫名稱.sql
tar czvf forum`date +%Y%m%d`.sql.tar.gz forum.sql
用 vi 在 /etc/crontab 增加一行(其它內容不管), 內容如下
[root@localhost ~]# vi /etc/crontab
59 23 * * * root /root/MySQLDump.sh
==================================================
用 vi 編輯 /root/MySQLDump.sh 和 /etc/crontab , 內容如下
代碼:
[root@localhost ~]# cat /root/MySQLDump.sh
cd /var/lib/mysql
mysqlcheck -a -c -o -r -uroot -p密碼 -h localhost 資料庫名稱
mysqldump forum -uroot -p密碼 -h localhost > 資料庫名稱.sql
tar czvf forum`date +%Y%m%d`.sql.tar.gz forum.sql
用 vi 在 /etc/crontab 增加一行(其它內容不管), 內容如下
代碼:
[root@localhost ~]# vi /etc/crontab
59 23 * * * root /root/MySQLDump.sh
(**+3).).
*ZH__"TW--**MySQL 5.1.71 資料庫同步**(Replication)-*主從模式*(CentOS 6.5 x86_64)~!^
+ + (3)**USA--EN"--* MySQL 5.1.71 database synchronization * (Replication) - ** master-slave mode ** (CentOS 6.5 x86_64) ~^
+ + (3) **Macau)FDZ" - *PT "--* MySQL 5.1.71 banco de dados de sincronização * (replicação) - ** modo mestre-escravo ** (CentOS 6.5 x86_64) ~^
+ + (3)**KO**--"MySQL은 5.1.71 데이터베이스 동기화 "(복제) - *마스터 - *슬레이브 모드* (CentOS에 6.5 x86_64의)~^
[] MySQL 5.1.71 資料庫同步(Replication)-主從模式(CentOS 6.5 x86_64)
參考
16.1.1. How to Set Up Replication
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
環境：
server1 : CentOS 6.5 x86_64 , IP: 192.168.128.101
server2 : CentOS 6.5 x86_64 , IP: 192.168.128.102
CentOS 6.5 的 MySQL 是 5.1.71 版
[root@centos1 ~]# yum list | grep mysql-server
mysql-server.x86_64 5.1.71-1.el6 base
[root@centos1 ~]#
**ZH__TW"--主從模式：A->B (A有新增資料，B也會有)
開始安裝
----------------------------------------------------------------------------
先在兩台上都安裝啟動 MySQL，暫時先關閉防火牆
參考
16.1.1. How to Set Up Replication
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
環境：
server1 : CentOS 6.5 x86_64 , IP: 192.168.128.101
server2 : CentOS 6.5 x86_64 , IP: 192.168.128.102
CentOS 6.5 的 MySQL 是 5.1.71 版
[root@centos1 ~]# yum list | grep mysql-server
mysql-server.x86_64 5.1.71-1.el6 base
[root@centos1 ~]#
**ZH__TW"--主從模式：A->B (A有新增資料，B也會有)
開始安裝
----------------------------------------------------------------------------
先在兩台上都安裝啟動 MySQL，暫時先關閉防火牆
==========================================
*=========================================
*USA--EN*--Master-slave mode: A-> B (A has additional information, B will have)
To start the installation
-------------------------------------------------- --------------------------
Are installed on both the first start MySQL, temporarily turn off the firewall
==============================================================
*PT))Modo mestre-escravo: A-> B (A tem informações adicionais, B terá)
para iniciar a instalação
-------------------------------------------------- --------------------------
São instalados em ambos a primeira partida MySQL, desligar temporariamente o firewall.
============================================================
**KO*----)마스터 - 슬레이브 모드 : A-> B (A가 자세한 내용은 B가있을 것이다있다)
설치를 시작하려면
-------------------------------------------------- --------------------------
모두 처음 시작 MySQL을 설치되어 일시적으로 방화벽을 해제
========================================= *
yum -y install mysql mysql-server
service mysqld start
chkconfig mysqld on
# 替 MySQL 帳號 root 設定密碼為 123456 (依個人喜好)
/usr/bin/mysqladmin -u root password '123456'
service iptables stop
----------------------------------------------------------------------------
**zh--TW"--假設要同步的資料庫是 backup
Server1 (MySQL Master, 192.168.128.101)上
1. 建立同步用帳號和要同步的資料庫backup，
讓另一台主機192.168.128.102上的root帳號在這台上有REPLICATION SLAVE權限
(-u 參數後面是帳號，-p 參數後面是密碼)
----------------------------------------------------------
yum -y install mysql mysql-server
service mysqld start
chkconfig mysqld on
# 替 MySQL 帳號 root 設定密碼為 123456 (依個人喜好)
/usr/bin/mysqladmin -u root password '123456'
service iptables stop
----------------------------------------------------------------------------
**zh--TW"--假設要同步的資料庫是 backup
Server1 (MySQL Master, 192.168.128.101)上
1. 建立同步用帳號和要同步的資料庫backup，
讓另一台主機192.168.128.102上的root帳號在這台上有REPLICATION SLAVE權限
(-u 參數後面是帳號，-p 參數後面是密碼)
----------------------------------------------------------
-------------------------------
*USA)EN*--
Suppose you want to synchronize the database is backup
Server1 (MySQL Master, 192.168.128.101) on
1. Establish synchronization with the account and database backup to synchronize,
Let another host on 192.168.128.102 root account in this stage have REPLICATION SLAVE privilege
(-u parameter is behind the account,-p parameter is behind a password)
-------------------------------
**PT(Macau)FDZ"---
Suponha que você deseja sincronizar o banco de dados é o backup
Server1 (MySQL Mestre, 192.168.128.101 ) em
1. Estabelecer a sincronização com a conta e de backup de banco de dados para sincronizar ,
Que outro host em conta root 192.168.128.102 nesta fase têm REPLICATION SLAVE privilégio
( parâmetro -U está por trás da conta parâmetro, -p está por trás de uma senha)
-------------------------------
**KO**--)
데이터베이스를 동기화한다고 가정 백업은
서버 1 (MySQL의 마스터, 192.168.128.101)에
1). 동기화 할 계정 및 데이터베이스 백업과 동기화를 구축,
이 단계에서 192.168.128.102 루트 계정에 다른 호스트 복제 SLAVE 권한이하자
(-U 매개 변수는 계정 뒤에,-p 매개 변수는 암호 뒤에)
================================================
*ZH-TW"--"其他以下的格式*請各位用家*-
-将"文字==>複製上谷歌翻繹器上==>便能譯出全文精要喔,
亦不需要"我的編文越來越長"--->'長得'-="像大象的鼻子"喔!哈哈^^
*USA--EN"--" other of the following formats * Please use the home *
- The "Text ==> Copy on Google turned on Yi is ==> full text can be translated Essentials Oh, not need," I Man getting longer compilation "---> 'look' -
=" like an elephant's nose, "Oh haha ^ ^!
*PT(Macau)FDZ-"--" outro dos seguintes formatos * Utilize a casa *
- O "texto ==> Copiar no Google ligado Yi é ==>
texto completo pode ser traduzido Essentials Ah, não precisa,
" Eu Homem que começ mais compilação "--->" olhar "-
=" como o nariz de um elefante, "Oh haha ^ ^!
**KO**---"다음과 같은 형식의 다른 * 홈 *을 사용하십시오 - 나는 "문자 ==>
구글에 복사 이순신에 설정 ==> 전체 텍스트의 요점을 번역 할 수 있습니다 오,, 필요가 없습니다" 남자가 더 이상 컴파일을 받고 "--->
'모양'-="코끼리의 코처럼,"하하 아 ^ ^!
*
========================================================[root@server1 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.71 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'root'@'192.168.128.102' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> create database backup;
Query OK, 1 row affected (0.00 sec)
mysql> \q
Bye
[root@server1 ~]#
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
可用
mysql> GRANT FILE ON *.* TO 'root'@'192.168.128.102';
或
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.128.102';
替代
PS :
如果 backup 資料庫已經存在使用，請用 mysqldump 將資料庫匯出，
[root@server1 ~]# mysqldump backup -uroot -p123456 > backup.sql
在 Server2 上把 backup 資料庫匯入，然後進行工作
[root@server2 ~]# mysql backup -uroot -p123456 < backup.sql
否則 Server1 原來 backup 已經存在的資料，是不會同步過去的，只會同步新增的資料
如果要備份整個資料庫，可用
[root@server1 ~]# mysqldump -uroot -p123456 --all-databases --add-drop-database --lock-all-tables > fulldb.sql
[root@server2 ~]# mysql -uroot -p123456 < fulldb.sql
或把Server2 上/var/lib/mysql中檔案砍光，把 Server1 上 /var/lib/mysql 整個目錄拷貝覆蓋掉 Server2 上的 (MySQL 的版本最好相同，否則可能會毀掉資料庫)
或執行
[root@server1 ~]# rsync -rvlHpogDtS --delete -e ssh /var/lib/mysql/. root@192.168.128.102:/var/lib/mysql/.
以本機器/var/lib/mysql完全同步到另一台的
2. 修改 /etc/my.cnf，在[mysqld]中增加server-id和log-bin(這兩個必須)，binlog-do-db指定需要日誌的資料庫(非必須)
[mysqld]
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
#設定一個編號
server-id=1
#讓別機器同步用的資料寫入此log中
log-bin=mysql-bin
#讓別機器同步用的資料庫backup (不設定這行，表示所有資料庫)
binlog-do-db=backup
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3.重新啟動資料庫，讓新建的帳號和my.cnf生效
[root@server1 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]#
4.檢查日誌情況，應該會顯示某些東西(顯示Empty則失敗)
[root@server1 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.71-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | backup | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
[root@server1 ~]#
----------------------------------------------------------------------------
Server2 (MySQL Slave, 192.168.128.102)上
1.建立要同步的資料庫
[root@server2 ~]# mysql -e "create database backup;" -uroot -p123456
2. 修改 /etc/my.cnf，在[mysqld]中增加下面資訊
[mysqld]
#定期去192.168.128.101抓資料用的帳號、密碼、port、間隔時間、抓哪個資料庫(不設則抓所有資料庫)
server-id=2
master-host=192.168.128.101
master-user=root
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=backup
3.重新啟動資料庫
[root@server2 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server2 ~]#
4.檢查日誌情況，應該會顯示某些東西
(注意，在 server1 指令是 show master status，此處為 slave )
[root@server2 ~]# mysql -e "show slave status;" -uroot -p123456
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 192.168.128.101 | root | 3306 | 60 | mysql-bin.000001 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000001 | Yes | Yes | backup | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
[root@server2 ~]#
----------------------------------------------------------------------------
現在可以測試了，在 Server1 上 backup 資料庫隨便新增東西，Server2上backup資料庫也會出現(應該不會超過60秒)。
如果無法正常運作，請檢視兩台機器的/var/log/mysqld.log檔案中有關[ERROR]的部分。
當一台電腦重新啟動MySQL時，請等它啟動完後成，再去重新啟動另外一台，否則會有錯誤訊息。
如果mysqld.log不是必須保留的(或把它另外先備份)，可以把它先清空，然後才執行service mysqld restart，這樣比較容易看出是否有錯誤訊息。
(網路上文章說，如果要修改slave的設定，要先刪除/var/lib/mysql/master.info檔案，否則無法生效，但是好像不需要)
Server1 上建立 students 資料表
[root@server1 ~]# mysql -e "use backup; create table students(no int, cname varchar(10)); show tables;" -uroot -p123456
+------------------+
| Tables_in_backup |
+------------------+
| students |
+------------------+
[root@server1 ~]#
[root@server1 ~]# mysql -e "show master status;" -uroot -p123456
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 219 | backup | |
+------------------+----------+--------------+------------------+
[root@server1 ~]# mysql -e "use backup; insert into students values ('01', 'John'); " -uroot -p123456
[root@server1 ~]# mysql -e "show master status;" -uroot -p123456
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 326 | backup | |
+------------------+----------+--------------+------------------+
到 Server2 上檢查
[root@server2 ~]# mysql -e "use backup; show tables;" -uroot -p123456
+------------------+
| Tables_in_backup |
+------------------+
| students |
+------------------+
[root@server2 ~]#
[root@server2 ~]# mysql -e "show slave status;" -uroot -p123456
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 192.168.128.101 | root | 3306 | 60 | mysql-bin.000001 | 219 | mysqld-relay-bin.000002 | 364 | mysql-bin.000001 | Yes | Yes | backup | | | | | | 0 | | 0 | 219 | 520 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
[root@server2 ~]#
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.71 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'root'@'192.168.128.102' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> create database backup;
Query OK, 1 row affected (0.00 sec)
mysql> \q
Bye
[root@server1 ~]#
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.128.102';
可用
mysql> GRANT FILE ON *.* TO 'root'@'192.168.128.102';
或
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.128.102';
替代
PS :
如果 backup 資料庫已經存在使用，請用 mysqldump 將資料庫匯出，
[root@server1 ~]# mysqldump backup -uroot -p123456 > backup.sql
在 Server2 上把 backup 資料庫匯入，然後進行工作
[root@server2 ~]# mysql backup -uroot -p123456 < backup.sql
否則 Server1 原來 backup 已經存在的資料，是不會同步過去的，只會同步新增的資料
如果要備份整個資料庫，可用
[root@server1 ~]# mysqldump -uroot -p123456 --all-databases --add-drop-database --lock-all-tables > fulldb.sql
[root@server2 ~]# mysql -uroot -p123456 < fulldb.sql
或把Server2 上/var/lib/mysql中檔案砍光，把 Server1 上 /var/lib/mysql 整個目錄拷貝覆蓋掉 Server2 上的 (MySQL 的版本最好相同，否則可能會毀掉資料庫)
或執行
[root@server1 ~]# rsync -rvlHpogDtS --delete -e ssh /var/lib/mysql/. root@192.168.128.102:/var/lib/mysql/.
以本機器/var/lib/mysql完全同步到另一台的
2. 修改 /etc/my.cnf，在[mysqld]中增加server-id和log-bin(這兩個必須)，binlog-do-db指定需要日誌的資料庫(非必須)
[mysqld]
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
#設定一個編號
server-id=1
#讓別機器同步用的資料寫入此log中
log-bin=mysql-bin
#讓別機器同步用的資料庫backup (不設定這行，表示所有資料庫)
binlog-do-db=backup
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3.重新啟動資料庫，讓新建的帳號和my.cnf生效
[root@server1 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]#
4.檢查日誌情況，應該會顯示某些東西(顯示Empty則失敗)
[root@server1 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.71-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | backup | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
[root@server1 ~]#
----------------------------------------------------------------------------
Server2 (MySQL Slave, 192.168.128.102)上
1.建立要同步的資料庫
[root@server2 ~]# mysql -e "create database backup;" -uroot -p123456
2. 修改 /etc/my.cnf，在[mysqld]中增加下面資訊
[mysqld]
#定期去192.168.128.101抓資料用的帳號、密碼、port、間隔時間、抓哪個資料庫(不設則抓所有資料庫)
server-id=2
master-host=192.168.128.101
master-user=root
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=backup
3.重新啟動資料庫
[root@server2 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server2 ~]#
4.檢查日誌情況，應該會顯示某些東西
(注意，在 server1 指令是 show master status，此處為 slave )
[root@server2 ~]# mysql -e "show slave status;" -uroot -p123456
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 192.168.128.101 | root | 3306 | 60 | mysql-bin.000001 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000001 | Yes | Yes | backup | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
[root@server2 ~]#
----------------------------------------------------------------------------
現在可以測試了，在 Server1 上 backup 資料庫隨便新增東西，Server2上backup資料庫也會出現(應該不會超過60秒)。
如果無法正常運作，請檢視兩台機器的/var/log/mysqld.log檔案中有關[ERROR]的部分。
當一台電腦重新啟動MySQL時，請等它啟動完後成，再去重新啟動另外一台，否則會有錯誤訊息。
如果mysqld.log不是必須保留的(或把它另外先備份)，可以把它先清空，然後才執行service mysqld restart，這樣比較容易看出是否有錯誤訊息。
(網路上文章說，如果要修改slave的設定，要先刪除/var/lib/mysql/master.info檔案，否則無法生效，但是好像不需要)
Server1 上建立 students 資料表
[root@server1 ~]# mysql -e "use backup; create table students(no int, cname varchar(10)); show tables;" -uroot -p123456
+------------------+
| Tables_in_backup |
+------------------+
| students |
+------------------+
[root@server1 ~]#
[root@server1 ~]# mysql -e "show master status;" -uroot -p123456
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 219 | backup | |
+------------------+----------+--------------+------------------+
[root@server1 ~]# mysql -e "use backup; insert into students values ('01', 'John'); " -uroot -p123456
[root@server1 ~]# mysql -e "show master status;" -uroot -p123456
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 326 | backup | |
+------------------+----------+--------------+------------------+
到 Server2 上檢查
[root@server2 ~]# mysql -e "use backup; show tables;" -uroot -p123456
+------------------+
| Tables_in_backup |
+------------------+
| students |
+------------------+
[root@server2 ~]#
[root@server2 ~]# mysql -e "show slave status;" -uroot -p123456
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 192.168.128.101 | root | 3306 | 60 | mysql-bin.000001 | 219 | mysqld-relay-bin.000002 | 364 | mysql-bin.000001 | Yes | Yes | backup | | | | | | 0 | | 0 | 219 | 520 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
[root@server2 ~]#
=========================================================
**USA--EN*--
[] MySQL 5.1.71 Database Synchronization (Replication) - Dual Active mode (CentOS 6.5 x86_64)
Reference -
16.1.1. How to Set Up Replication
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
server1: CentOS 6.5 x86_64, IP: 192.168.128.101
server2: CentOS 6.5 x86_64, IP: 192.168.128.102
CentOS MySQL 6.5 version is 5.1.71
[root @ centos1 ~] # yum list | grep mysql-server
mysql-server.x86_64 5.1.71-1.el6 base
[root @ centos1 ~] #
-------------------------------------------------- --------------------------
Are installed on both the first start MySQL, temporarily turn off the firewall
yum-y install mysql mysql-server
service mysqld start
chkconfig mysqld on
# Set a password for MySQL root account is 123456 ( according to personal preference )
/ usr / bin / mysqladmin-u root password '123456 '
service iptables stop
-------------------------------------------------- --------------------------
On Server1
1 . Establish synchronization with the account and the database to be synchronized , so that the root account on another host 192.168.128.102 in this stage have REPLICATION SLAVE privilege
[root @ server1 ~] # mysql
mysql> CREATE USER 'root' @ '192 .168.128.102 'IDENTIFIED BY'';
Query OK, 0 rows affected (0.00 sec)
. mysql> GRANT REPLICATION SLAVE ON * * TO 'root' @ '192 .168.128.102 ';
Or
. mysql> GRANT FILE ON * * TO 'root' @ '192 .168.128.102 ';
Or
. mysql> GRANT ALL PRIVILEGES ON * * TO 'root' @ '192 .168.128.102 ';
Let permissions settings to take effect ( or restart MySQL also available )
mysql> FLUSH PRIVILEGES;
Create a new empty database ( called backup)
mysql> create database backup;
mysql> \ q
(2) modify / etc / my.cnf, add the following information in the [mysqld] in
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = backup
# Grab data regularly to 192.168.128.102
master-host = 192.168.128.102
master-user = root
master-password =
master-port = 3306
master-connect-retry = 60
replicate-do-db = backup
2 my.cnf restart the database to allow the entry into force
[root @ server1 ~] # service mysqld restart
PS: Because Server2 has not been set , so the / var / log / mysqld.log may be wrong
Etc. After Server2 completion, restart , you can restart once Server1
-------------------------------------------------- --------------------------
On Server2
1 . Establish synchronization with the account and the database to be synchronized , so that the root account on another host 192.168.128.101 in this stage have REPLICATION SLAVE privilege
[root @ server1 ~] # mysql
mysql> CREATE USER 'root' @ '192 .168.128.101 'IDENTIFIED BY'';
Query OK, 0 rows affected (0.00 sec)
. mysql> GRANT REPLICATION SLAVE ON * * TO 'root' @ '192 .168.128.101 ';
Query OK, 0 rows affected (0.00 sec)
(2) modify / etc / my.cnf, add the following information in the [mysqld] in
( Note , log-bin settings and can not be the same on Server1 )
[mysqld]
server-id = 2
log-bin = mysql-bin2
binlog-do-db = backup
# Grab data regularly to 192.168.128.101
master-host = 192.168.128.101
master-user = root
master-password =
master-port = 3306
master-connect-retry = 60
replicate-do-db = backup
2 Restart the database
[root @ server2 ~] # service mysqld restart
Server1 started once again
[root @ server1 ~] # service mysqld restart
-------------------------------------------------- --------------------------
Can any one of the backup database or data in Table add two machines , the other one should also appear.
If it fails
Please put on Server1 mysql-bin. * And mysqld-relay-bin. * Full cut
Please put on Server2 mysql-bin2. * And mysqld-relay-bin. * Full cut
rsync-rvlHpogDtS -. delete-e ssh / var / lib / mysql / root@192.168.128.102 :/ var / lib / mysql /.
Re-establish the account permissions test
==========================================
2 ) .
[ + + ] How to command - " daily automatic backup MySQL" - approach ? !
See below - ]
MySQL daily automatic backup method ---
Editing with vi / root / MySQLDump.sh and / etc / crontab, which reads as follows
Code:
[root @ localhost ~] # cat / root / MySQLDump.sh
cd / var / lib / mysql
mysqlcheck-a-c-o-r-uroot-p password -h localhost database name
mysqldump forum-uroot-p password -h localhost> database name . sql
tar czvf forum `date +% Y% m% d`. sql.tar.gz forum.sql
-------------------------------------------------- ----------------------------
In using vi / etc / crontab add a line ( regardless of other content ) , as follows
Code:
[root @ localhost ~] # vi / etc / crontab
59 23 *** root / root / MySQLDump.sh
----------------------------------------
* http://www.melodytoyssexy.blogspot.com/2014/04/zhtw-mysql-5171-replication-centos-65.html *
===============================================
**PT(Macau)FDZ"--
[] MySQL 5.1.71 Sincronização de Dados (replicação) - Modo ativo duplo (CentOS 6.5 x86_64)
Referência -
16.1.1. Como Configurar a Replicação
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
server1: CentOS 6.5 x86_64, IP: 192.168.128.101
server2: CentOS 6.5 x86_64, IP: 192.168.128.102
CentOS MySQL versão 6.5 é 5.1.71
[Root @ centos1 ~] # yum list | grep mysql-server
mysql-5.1.71-base de server.x86_64 1.el6
[Root @ centos1 ~] #
-------------------------------------------------- --------------------------
São instalados em ambos a primeira partida MySQL, desligar temporariamente o firewall
yum-y install mysql mysql-server
início mysqld serviço
chkconfig mysqld on
# Defina uma senha para a conta root do MySQL é 123456 (de acordo com a preferência pessoal)
/ Usr / bin / mysqladmin-u root password '123456 '
iptables serviço parar
-------------------------------------------------- --------------------------
Em Server1
1. Estabelecer a sincronização com a conta eo banco de dados a ser sincronizada, de modo que a conta root em outro 192.168.128.102 anfitrião nesta fase têm REPLICATION SLAVE privilégio
[Root @ server1 ~] # mysql
mysql> CREATE USER 'root' @ '192 .168.128.102 'identificado por'';
Query OK, 0 linhas afetadas (0,00 segundos)
. Mysql> GRANT REPLICATION SLAVE ON ** TO 'root' @ '192 .168.128.102 ';
Ou
. Mysql> GRANT ARQUIVO NO ** TO 'root' @ '192 .168.128.102 ';
Ou
. Mysql> GRANT ALL PRIVILEGES ON ** TO 'root' @ '192 .168.128.102 ';
Vamos configurações de permissão para entrar em vigor (ou reiniciar MySQL também está disponível)
mysql> FLUSH PRIVILEGES;
Criar um novo banco de dados vazio (chamado de backup)
mysql> criar um backup de banco de dados;
mysql> \ q
(2) modificar o / etc / my.cnf, adicione as seguintes informações no [mysqld] no
[Mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = Backup
Dados # Grab regularmente para 192.168.128.102
master-host = 192.168.128.102
master-user = root
master-password =
master-port = 3306
master-connect-retry = 60
replicate-do-db = Backup
2 my.cnf reiniciar o banco de dados para permitir a entrada em vigor
[Root @ server1 ~] # service mysqld restart
PS: Porque Server2 não foi definido, de modo que o / var / log / mysqld.log pode estar errado
Etc. Depois Server2 conclusão, reiniciar, você pode reiniciar uma vez Server1
-------------------------------------------------- --------------------------
Em Server2
1. Estabelecer a sincronização com a conta eo banco de dados a ser sincronizada, de modo que a conta root em outro 192.168.128.101 anfitrião nesta fase têm REPLICATION SLAVE privilégio
[Root @ server1 ~] # mysql
mysql> CREATE USER 'root' @ '192 .168.128.101 'identificado por'';
Query OK, 0 linhas afetadas (0,00 segundos)
. Mysql> GRANT REPLICATION SLAVE ON ** TO 'root' @ '192 .168.128.101 ';
Query OK, 0 linhas afetadas (0,00 segundos)
(2) modificar o / etc / my.cnf, adicione as seguintes informações no [mysqld] no
(Nota, log-bin configurações e não pode ser o mesmo em Server1)
[Mysqld]
server-id = 2
log-bin = mysql-BIN2
binlog-do-db = Backup
Dados # Grab regularmente para 192.168.128.101
master-host = 192.168.128.101
master-user = root
master-password =
master-port = 3306
master-connect-retry = 60
replicate-do-db = Backup
2 Reinicie o banco de dados
[Root @ server2 ~] # service mysqld restart
Server1 começou mais uma vez
[Root @ server1 ~] # service mysqld restart
-------------------------------------------------- --------------------------
Pode qualquer um do banco de dados de backup ou dados da Tabela adicionar duas máquinas, o outro também deve aparecer.
Se falhar
Por favor, coloque no Server1 mysql-bin. * E mysqld-relay-bin. * Corte completa
Por favor, coloque no Server2 mysql-BIN2. * E mysqld-relay-bin. * Corte completa
rsync-rvlHpogDtS -. delete-e ssh / var / lib / mysql / root@192.168.128.102 :/ var / lib / mysql /.
Restabelecer o teste de permissões de conta
========================================
[+ +] 2).Como comandar - " MySQL backup automático diário " - abordagem !
Veja abaixo -]
Método de backup diário automático MySQL ---
Editando com o vi / root / MySQLDump.sh e / etc / crontab, que diz o seguinte
Código :
[root @ localhost ~] # cat / root / MySQLDump.sh
cd / var / lib / mysql
mysqlcheck -a- c -o- r- uroot -p senha -h nome do banco localhost
mysqldump fórum de uroot -p senha -h localhost > nome do banco de dados. sql
tar czvf fórum Data `+ % Y % m% d` . sql.tar.gz forum.sql
-------------------------------------------------- ----------------------------
Ao usar vi / etc / crontab adicionar uma linha (independentemente de outros conteúdos ), como segue
Código :
[root @ localhost ~] # vi / etc / crontab
59 23 *** root / root / MySQLDump.sh
````````````
http://www.melodytoyssexy.blogspot.com/2014/04/zhtw-mysql-5171-replication-centos-65.html
==================================*
**KO*--
[] MySQL은 5.1.71 데이터베이스 동기화 (복제) - 듀얼 액티브 모드 ( CentOS에 6.5 x86_64의 )
참고 -
16.1.1 . 복제를 설정 하는 방법
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
서버 1 : CentOS에 6.5 x86_64의 , IP : 192.168.128.101
server2에 : CentOS에 6.5 x86_64의 , IP : 192.168.128.102
CentOS에 MySQL의 6.5 버전은 5.1.71 이다
[ 루트 @ centos1 ~ ] # 얌 목록 | 그렙 MySQL의 서버
MySQL의 server.x86_64 5.1.71 - 1.el6 베이스
[ 루트 @ centos1 ~ ] #
-------------------------------------------------- --------------------------
모두 처음 시작 MySQL을 설치되어 일시적으로 방화벽 을 해제
냠 - y를 설치 MySQL은 MySQL의 서버
서비스 의 mysqld 시작
chkconfig를 은 에 mysqld이
# 설정 MySQL의 루트 계정에 대한 암호 는 ( 개인의 취향 에 따라) 123456
를 / usr / 빈 / mysqladmin는 -U 루트 패스워드 '123456 '
서비스 의 iptables 중지
-------------------------------------------------- --------------------------
서버 1 에
이 단계 에서 다른 호스트 192.168.128.102 의 root 계정 은 복제 SLAVE 권한을 가질 수 있도록 1. , 동기화 할 수있는 계정과 데이터베이스와 동기화 를 설정
[ 루트 @ 서버 1 ~ ] # mysql을
MySQL은 > '192 .168.128.102 ' 동정 ' @ 사용자 ' 루트 ' CREATE ;
쿼리 OK 영향을 0 행 (0.00 초)
. ** '192 .168.128.102 @ ' 루트 ' TO ' 에서 MySQL > GRANT 복제 SLAVE ;
또는
. ** 에서 MySQL > GRANT 파일 '192 .168.128.102 @ ' 루트 ' ' TO ;
또는
. MySQL은 > ON ** '192 .168.128.102 @ ' 루트 '에서 ' 모든 권한을 부여 ;
적용 (또는 MySQL은 또한 유효 다시 시작 ) 할 수있는 권한 설정을 하자
MySQL은 > FLUSH PRIVILEGES 를 ;
( 백업 라는 ) 새로운 빈 데이터베이스를 만듭니다
MySQL은 > 데이터베이스 백업을 만드는 ;
MySQL은 > \ Q
(2) 은 / etc / my.cnf를 수정 ,[ mysqld를 ] 에있는 다음 정보를 추가
[ Mysqld의 ]
서버 ID = 1
로그 빈 = mysql을 - 빈
바이너리 로그 -DO- DB = 백업
192.168.128.102 에 정기적 # 잡아 데이터
마스터 호스트 = 192.168.128.102
마스터 사용자 = 루트
마스터 암호 =
마스터 포트 = 3306
마스터 연결 재시도 = 60
복제 -DO- DB = 백업
2 발효 를 할 수 있도록 데이터베이스를 다시 시작 my.cnf에
[ 루트 @ 서버 1 ~ ] # 서비스 mysqld를 다시 시작
PS : 서버 2 가 설정되어 있지 않은 , 그래서/ var에 / 로그 때문에 / mysqld.log 이 잘못 될 수있다
등 서버 2 완료 , 다시 시작되면 , 한 번 서버 1 을 다시 시작할 수 있습니다
-------------------------------------------------- --------------------------
서버 2
이 단계 에서 다른 호스트 192.168.128.101 의 root 계정 은 복제 SLAVE 권한을 가질 수 있도록 1. , 동기화 할 수있는 계정과 데이터베이스와 동기화 를 설정
[ 루트 @ 서버 1 ~ ] # mysql을
MySQL은 > '192 .168.128.101 ' 동정 ' @ 사용자 ' 루트 ' CREATE ;
쿼리 OK 영향을 0 행 (0.00 초)
. ** '192 .168.128.101 @ ' 루트 ' TO ' 에서 MySQL > GRANT 복제 SLAVE ;
쿼리 OK 영향을 0 행 (0.00 초)
(2) 은 / etc / my.cnf를 수정 ,[ mysqld를 ] 에있는 다음 정보를 추가
( 참고 , 설정 - 빈 로그 서버 1 에 동일 할 수 없습니다 )
[ Mysqld의 ]
서버 ID = 2
로그 빈 = mysql을 - BIN2
바이너리 로그 -DO- DB = 백업
192.168.128.101 에 정기적 # 잡아 데이터
마스터 호스트 = 192.168.128.101
마스터 사용자 = 루트
마스터 암호 =
마스터 포트 = 3306
마스터 연결 재시도 = 60
복제 -DO- DB = 백업
2 데이터베이스 를 다시 시작합니다
[ 루트 @ server2에 ~ ] # 서비스 mysqld를 다시 시작
서버 1 을 다시 한 번 시작
[ 루트 @ 서버 1 ~ ] # 서비스 mysqld를 다시 시작
-------------------------------------------------- --------------------------
표 에서 백업 데이터베이스 또는 데이터 중 하나가 두 개의 시스템을 추가 할 수 있습니다 , 다른 하나 는 나타나야합니다.
그것은 실패 할 경우
서버 1 MySQL의 칸 에 넣어주세요 . * 그리고 mysqld를 릴레이 단 . * 전체 컷
서버 2 MySQL의 BIN2 에 넣어주세요 . * 그리고 mysqld를 릴레이 단 . * 전체 컷
rsync를 - rvlHpogDtS - . 삭제 - 전자 SSH 로 / var / lib / mysql을 / root@192.168.128.102 :/ var에 / lib 디렉토리 / mysql을 / 를 .
계정 의 권한을 검사 에게 다시 설정
==================================================
2).
[+ + ] 어떻게 명령 - " 매일 자동 백업 MySQL의 " - 방법 !
아래를 참조 -]
MySQL을 매일 자동 백업 방법 ---
VI / 루트 / MySQLDump.sh 다음과 같이 읽어 은 / etc / crontab을 사용하여 편집
코드 :
[ 루트 @ localhost를 ~ ] # 고양이 / 루트 / MySQLDump.sh
CD 로 / var / lib / mysql을
mysqlcheck의 - ACOR - uroot -P 암호 -H 로컬 호스트 데이터베이스 이름
mysqldump는 포럼 - uroot -P 암호 -H 로컬 호스트 > 데이터베이스 이름 . SQL
타르 포럼 czvf ` 날짜 + % Y %의 m % d의 ` . sql.tar.gz forum.sql
-------------------------------------------------- ----------------------------
다음과 바이올렛 은 / etc / crontab을 을 사용하여 , 선 ( 에 관계없이 기타 콘텐츠 )를 추가
코드 :
[ 루트 @ localhost를 ~ ] # 바이올렛 은 / etc / crontab을
59 23 *** 루트 / 루트 / MySQLDump.sh
````````````
http://www.melodytoyssexy.blogspot.com/2014/04/zhtw-mysql-5171-replication-centos-65.html
==================================
***[] MySQLeun 5.1.71 deiteobeiseu dong-gihwa (bogje) - dyueol aegtibeu modeu ( CentOSe 6.5 x86_64ui )*~!^^
chamgo -
16.1.1 . bogjeleul seoljeong haneun bangbeob
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
seobeo 1 : CentOSe 6.5 x86_64ui , IP : 192.168.128.101
server2e : CentOSe 6.5 x86_64ui , IP : 192.168.128.102
CentOSe MySQLui 6.5 beojeon-eun 5.1.71 ida
[ luteu @ centos1 ~ ] # yam moglog | geuleb MySQLui seobeo
MySQLui server.x86_64 5.1.71 - 1.el6 beiseu
[ luteu @ centos1 ~ ] #
-------------------------------------------------- --------------------------
modu cheoeum sijag MySQLeul seolchidoeeo ilsijeog-eulo banghwabyeog eul haeje
nyam - yleul seolchi MySQLeun MySQLui seobeo
seobiseu ui mysqld sijag
chkconfigleul eun e mysqldi
# seoljeong MySQLui luteu gyejeong-e daehan amho neun ( gaein-ui chwihyang e ttala) 123456
leul / usr / bin / mysqladminneun -U luteu paeseuwodeu '123456 '
seobiseu ui iptables jungji
-------------------------------------------------- --------------------------
seobeo 1 e
i dangye eseo daleun hoseuteu 192.168.128.102 ui root gyejeong eun bogje SLAVE gwonhan-eul gajil su issdolog 1. , dong-gihwa hal su-issneun gyejeong-gwa deiteobeiseuwa dong-gihwa leul seoljeong
[ luteu @ seobeo 1 ~ ] # mysqleul
MySQLeun > '192 .168.128.102 ' dongjeong ' @ sayongja ' luteu ' CREATE ;
kwoli OK yeonghyang-eul 0 haeng (0.00 cho)
. ** '192 .168.128.102 @ ' luteu ' TO ' eseo MySQL > GRANT bogje SLAVE ;
ttoneun
. ** eseo MySQL > GRANT pail '192 .168.128.102 @ ' luteu ' ' TO ;
ttoneun
. MySQLeun > ON ** '192 .168.128.102 @ ' luteu 'eseo ' modeun gwonhan-eul buyeo ;
jeog-yong (ttoneun MySQLeun ttohan yuhyo dasi sijag ) hal su-issneun gwonhan seoljeong-eul haja
MySQLeun > FLUSH PRIVILEGES leul ;
( baeg-eob laneun ) saeloun bin deiteobeiseuleul mandeubnida
MySQLeun > deiteobeiseu baeg-eob-eul mandeuneun ;
MySQLeun > \ Q
(2) eun / etc / my.cnfleul sujeong ,[ mysqldleul ] eissneun da-eum jeongboleul chuga
[ Mysqldui ]
seobeo ID = 1
logeu bin = mysqleul - bin
baineoli logeu -DO- DB = baeg-eob
192.168.128.102 e jeong-gijeog # jab-a deiteo
maseuteo hoseuteu = 192.168.128.102
maseuteo sayongja = luteu
maseuteo amho =
maseuteo poteu = 3306
maseuteo yeongyeol jaesido = 60
bogje -DO- DB = baeg-eob
2 balhyo leul hal su issdolog deiteobeiseuleul dasi sijag my.cnfe
[ luteu @ seobeo 1 ~ ] # seobiseu mysqldleul dasi sijag
PS : seobeo 2 ga seoljeongdoeeo issji anh-eun , geulaeseo/ vare / logeu ttaemun-e / mysqld.log i jalmos doel su-issda
deung seobeo 2 wanlyo , dasi sijagdoemyeon , han beon seobeo 1 eul dasi sijaghal su issseubnida
-------------------------------------------------- --------------------------
seobeo 2
i dangye eseo daleun hoseuteu 192.168.128.101 ui root gyejeong eun bogje SLAVE gwonhan-eul gajil su issdolog 1. , dong-gihwa hal su-issneun gyejeong-gwa deiteobeiseuwa dong-gihwa leul seoljeong
[ luteu @ seobeo 1 ~ ] # mysqleul
MySQLeun > '192 .168.128.101 ' dongjeong ' @ sayongja ' luteu ' CREATE ;
kwoli OK yeonghyang-eul 0 haeng (0.00 cho)
. ** '192 .168.128.101 @ ' luteu ' TO ' eseo MySQL > GRANT bogje SLAVE ;
kwoli OK yeonghyang-eul 0 haeng (0.00 cho)
(2) eun / etc / my.cnfleul sujeong ,[ mysqldleul ] eissneun da-eum jeongboleul chuga
( chamgo , seoljeong - bin logeu seobeo 1 e dong-il hal su eobs-seubnida )
[ Mysqldui ]
seobeo ID = 2
logeu bin = mysqleul - BIN2
baineoli logeu -DO- DB = baeg-eob
192.168.128.101 e jeong-gijeog # jab-a deiteo
maseuteo hoseuteu = 192.168.128.101
maseuteo sayongja = luteu
maseuteo amho =
maseuteo poteu = 3306
maseuteo yeongyeol jaesido = 60
bogje -DO- DB = baeg-eob
2 deiteobeiseu leul dasi sijaghabnida
[ luteu @ server2e ~ ] # seobiseu mysqldleul dasi sijag
seobeo 1 eul dasi han beon sijag
[ luteu @ seobeo 1 ~ ] # seobiseu mysqldleul dasi sijag
-------------------------------------------------- --------------------------
pyo eseo baeg-eob deiteobeiseu ttoneun deiteo jung hanaga du gaeui siseutem-eul chuga hal su issseubnida , daleun hana neun natanayahabnida.
geugeos-eun silpae hal gyeong-u
seobeo 1 MySQLui kan e neoh-eojuseyo . * geuligo mysqldleul lillei dan . * jeonche keos
seobeo 2 MySQLui BIN2 e neoh-eojuseyo . * geuligo mysqldleul lillei dan . * jeonche keos
rsyncleul - rvlHpogDtS - . sagje - jeonja SSH lo / var / lib / mysqleul / root@192.168.128.102 :/ vare / lib dilegtoli / mysqleul / leul .
gyejeong ui gwonhan-eul geomsa ege dasi seoljeong
=============================
2).
[+ +] eotteohge myeonglyeong - " maeil jadong baeg-eob MySQLui ui " -
- bangbeob !
alaeleul chamjo -]
MySQLeun eul maeil jadong baeg-eob bangbeob ---
VI / luteu / MySQLDump.sh da-eumgwa gat-i ilg-eoeun eun / etc / crontab-eul eul sayonghayeo pyeonjib
kodeu :
[ luteu @ lokeol hoseuteu leul ~ ] # goyang-i / luteu / MySQLDump.sh
CD lo lo / var / lib / mysqleul eul
mysqlcheckui ui - ACOR - uroot -P amho -H lokeol hoseuteu deiteobeiseu ileum
mysqldumplo neun poleom - uroot -P amho -H lokeol hoseuteu > deiteobeiseu ileum SQL .
taleu poleom czvf ` naljja + % Y % 1 ui m % dui ui ui ` . Sql.tar.gz forum.sql
-------------------------------------------------- ----------------------------
da-eumgwa baiolles-eun eun / etc / crontab-eul eul-eul sayonghayeo , seon ( e gwangyeeobs-i gita kontencheu ) leul chuga
kodeu :
[ luteu @ lokeol hoseuteu leul ~ ] # baiolles-eun / deung / ui crontab eul
59 23 *** luteu / luteu / MySQLDump.sh
------------------------------------------------------------------------------------------------
http://www.melodytoyssexy.blogspot.com/2014/04/zhtw-mysql-5171-replication-centos-65.html
==================================*
**分享介绍*ZH-TW'--"MySQL 5.1.71 資料庫同步"-(Replication)-*雙機互備模式*-(CentOS 6.5 x86_64)~++*2).*.[] 如何指令--"每天自動備份 MySQL "-方法 ~ 詳見如下--]~^"---**Share Introduction--*USA-EN*--"MySQL 5.1.71 database synchronization "- (Replication) -*Dual Active mode-!--(CentOS 6.5 x86_64)~[ + + ]** How to command---"daily automatic backup MySQL"--approach?! See below - ]....!^^"--- * Introdução *FDZ( Macau) - *PT *-- " MySQL 5.1.71 sincronização de dados " - ( replicação) - * Modo ativo duplo * - ( CentOS 6.5 x86_64 ) ~ +] 2).Como comandar--" MySQL backup automático diário "--abordagem ! "--- **공유 소개 * KO** - " MySQL은 5.1.71 데이터베이스 동기화 " - (복제) - * 듀얼 액티브 모드 * - ! ( CentOS에 6.5 x86_64의 ) ~[+ + ] *어떻게 명령 --"매일 자동 백업 MySQL의 "- 방법 !"--**[] MySQLeun 5.1.71 deiteobeiseu dong-gihwa (bogje) - dyueol aegtibeu modeu ( CentOSe 6.5 x86_64ui )[+ +] eotteohge myeonglyeong - " maeil jadong baeg-eob MySQLui ui " -- bangbeob !!++(3).**ZH--TW"--*MySQL 5.1.71 資料庫同步*(Replication)-**主從模式**(CentOS 6.5 x86_64)~^ + + (3)**USA--EN"--* MySQL 5.1.71 database synchronization * (Replication) - ** master-slave mode ** (CentOS 6.5 x86_64) ~^ + + (3) **Macau)FDZ" - *PT "--* MySQL 5.1.71 banco de dados de sincronização * (replicação) - ** modo mestre-escravo ** (CentOS 6.5 x86_64) ~^ + + (3)**KO**--"MySQL은 5.1.71 데이터베이스 동기화 "(복제) - *마스터 - *슬레이브 모드* (CentOS에 6.5 x86_64의)~^^--
===Melody.Blog===THE END===>/
沒有留言:
張貼留言
if you like make fds, wellcome you here~~anytime***
my free place for everyones who want the good software,
come & download them~ wellcome!!