Mysql Master slaveレプリケーション
構成の手順(mysql 56を例に):
マスターデータベース:
my.ini 5.6修正
C:\Documents and Settings\AllUsers\Application Data\MySQL\my.ini
権限のあるユーザーの作成
>mysql -u root –p
--ユーザーの作成
mysql> create user jk;
--ユーザーの権限の追加
mysql> grant replication slave on *.* to jk identified by'パスワード';
mysqlを再起動するには:
netstop mysql56
netstart mysql56
Slaveデータベース
slave対応masterデータベースの構成
Rootログイン実行:
change master tomaster_host='192.168.1.33',
master_port=3306,master_user='jk',master_password='パスワード';
slaveをオンにします.
Start slave;
プライマリ・サーバにすでにアプリケーション・データが存在する場合、プライマリ・スレーブのレプリケーションを行う場合は、次の処理が必要です.
(1)メインデータベースはロック操作を行い、データを書き込み動作させない
mysql> FLUSH TABLES WITH READ LOCK;
(2)プライマリ・データベースのステータスの表示
mysql> show master status;
(3)FILEおよびPositionの値を記録する.
プライマリ・サーバのデータ・ファイル(全体/opt/mysql/dataディレクトリ)をセカンダリ・サーバにコピーし、tarアーカイブで圧縮してからサーバから解凍することを推奨します.
(4)プライマリ・データベースのロック解除
mysql> UNLOCK TABLES;
プライマリ・スレーブ・データベースでエラーが発生した場合の解決:
masterデータベース実行:show master statusG;
FileとPosition値の表示
slaveデータベースの実行:
slaveを停止します.
change master to master_log_file='master-bin.000004', master_log_pos=1018; start slaveを起動します.
テスト:
masterデータベースでデータベースを作成し、データを追加
slaveデータベースでクエリーできます.
公式サイト:
http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html
公式サイトの手順:
There are somegeneric tasks that are common to all replication setups:
· On the master, you must enable binary logging andconfigure a unique server ID. This might require a server restart. See Section 17.1.1.1, “Settingthe Replication Master Configuration”.(master binary logとserver IDをオンにして再起動)
· On each slave that you want to connect to the master, youmust configure a unique server ID. This might require a server restart. See Section 17.1.1.2, “Settingthe Replication Slave Configuration”.(slave構成serverID再起動)
· Optionally, create a separate user for your slaves to useduring authentication with the master when reading the binary log forreplication. See Section 17.1.1.3, “Creating a User for Replication”.(コピー用のユーザーを作成)
· Before creating a data snapshot or starting thereplication process, you should record the position of the binary log on themaster. You will need this information when configuring the slave so that theslave knows where within the binary log to start executing events. See Section 17.1.1.4,“Obtaining the Replication Master Binary Log Coordinates”.(masterのbinlogログ)
· If you already have data on your master and want to useit to synchronize your slave, you need to create a data snapshot to copy thedata to the slave. The storage engine you are using has an impact on how youcreate the snapshot. When you are using MyISAM, you must stopprocessing statements on the master to obtain a read-lock, then obtain itscurrent binary log coordinates and dump its data, before permitting the masterto continue executing statements. If you do not stop the execution ofstatements, the data dump and the master status information will not match,resulting in inconsistent or corrupted databases on the slaves. For moreinformation on replicating a MyISAM master, see Section 17.1.1.4, “Obtainingthe Replication Master Binary Log Coordinates”. If you are using InnoDB,you do not need a read-lock and a transaction that is long enough to transferthe data snapshot is sufficient. For more information, see Section 14.17, “InnoDB and MySQL Replication”.(ストレージエンジンの異なる操作、ロックテーブルなど)
· Configure the slave with settings for connecting to themaster, such as the host name, login credentials, and binary log file name andposition. (Slave構成接続マスターの情報)See Section 17.1.1.8, “Settingthe Master Configuration on the Slave”.
Note
Certain steps within the setup processrequire the SUPER privilege. If youdo not have this privilege, it might not be possible to enable replication.(権限が必要)
大拿demo:
It is possible to sync a database to a masterwhile the master is in active use. Commands starting "M:"are run onthe master, "S:"are run in the slave. Until the end, the slave stateis undefined. Thisworks for InnoDB tables, if you have set transactionality to REPEATABLE-READ.
(Urghh...I see no way to embed code into these posts. Hence the ugly "____"thing to indicate indentation. I suggest copying into something with anon-proportinal font, to make it easier to read)
マスターデータベース:
my.ini 5.6修正
C:\Documents and Settings\AllUsers\Application Data\MySQL\my.ini
######### , ，
[client]
port=3306
default-character-set=utf8
[mysqld]
port=3306
#character_set_server=utf8
character_set_server=utf8
#
basedir=C:\ProgramFiles\MySQL\mysql-5.6.16-win32
# data , data
datadir=C:\ProgramFiles\MySQL\mysql-5.6.16-win32\data
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ，
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#
#
log-bin=master-bin
#
log-bin-index=master.bin.index
# IP 192.168.1.33
server-id=33
######### 権限のあるユーザーの作成
>mysql -u root –p
--ユーザーの作成
mysql> create user jk;
--ユーザーの権限の追加
mysql> grant replication slave on *.* to jk identified by'パスワード';
mysqlを再起動するには:
netstop mysql56
netstart mysql56
Slaveデータベース
######### , ，
[client]
port=3306
default-character-set=utf8
[mysqld]
port=3306
#character_set_server=utf8
character_set_server=utf8
basedir=C:\ProgramFiles\MySQL\mysql-5.6.16-win32
datadir=C:\ProgramFiles\MySQL\mysql-5.6.16-win32\data
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ，
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
##192.168.1.22 ID The master and each slave must be configured witha unique ID
server-id=22
relay-log=slave-relay-log-bin
relay-log-index=slave-relay-log-bin.index
######### slave対応masterデータベースの構成
Rootログイン実行:
change master tomaster_host='192.168.1.33',
master_port=3306,master_user='jk',master_password='パスワード';
slaveをオンにします.
Start slave;
mysql> show slave status\G
==============================================
**************** 1. row *******************
Slave_IO_State:
Master_Host: 192.168.1.33
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 415
Relay_Log_File: localhost-relay-bin.000008
Relay_Log_Pos: 561
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: YES
Slave_SQL_Running: YES
Replicate_Do_DB:
…………… ……………
Master_Server_Id: 1
1 row in set (0.01 sec)
==============================================
Slave_IO_Running Slave_SQL_Running YES， 。
プライマリ・サーバにすでにアプリケーション・データが存在する場合、プライマリ・スレーブのレプリケーションを行う場合は、次の処理が必要です.
(1)メインデータベースはロック操作を行い、データを書き込み動作させない
mysql> FLUSH TABLES WITH READ LOCK;
(2)プライマリ・データベースのステータスの表示
mysql> show master status;
(3)FILEおよびPositionの値を記録する.
プライマリ・サーバのデータ・ファイル(全体/opt/mysql/dataディレクトリ)をセカンダリ・サーバにコピーし、tarアーカイブで圧縮してからサーバから解凍することを推奨します.
(4)プライマリ・データベースのロック解除
mysql> UNLOCK TABLES;
プライマリ・スレーブ・データベースでエラーが発生した場合の解決:
masterデータベース実行:show master statusG;
FileとPosition値の表示
mysql> show master status \G
*************************** 1. row ***************************
File: master-bin.000004
Position: 1018
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
slaveデータベースの実行:
slaveを停止します.
change master to master_log_file='master-bin.000004', master_log_pos=1018; start slaveを起動します.
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
mysql> change master to master_log_file='master-bin.000004', master_log_pos=1018;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> use proxy1テスト:
masterデータベースでデータベースを作成し、データを追加
slaveデータベースでクエリーできます.
公式サイト:
http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html
公式サイトの手順:
There are somegeneric tasks that are common to all replication setups:
· On the master, you must enable binary logging andconfigure a unique server ID. This might require a server restart. See Section 17.1.1.1, “Settingthe Replication Master Configuration”.(master binary logとserver IDをオンにして再起動)
· On each slave that you want to connect to the master, youmust configure a unique server ID. This might require a server restart. See Section 17.1.1.2, “Settingthe Replication Slave Configuration”.(slave構成serverID再起動)
· Optionally, create a separate user for your slaves to useduring authentication with the master when reading the binary log forreplication. See Section 17.1.1.3, “Creating a User for Replication”.(コピー用のユーザーを作成)
· Before creating a data snapshot or starting thereplication process, you should record the position of the binary log on themaster. You will need this information when configuring the slave so that theslave knows where within the binary log to start executing events. See Section 17.1.1.4,“Obtaining the Replication Master Binary Log Coordinates”.(masterのbinlogログ)
· If you already have data on your master and want to useit to synchronize your slave, you need to create a data snapshot to copy thedata to the slave. The storage engine you are using has an impact on how youcreate the snapshot. When you are using MyISAM, you must stopprocessing statements on the master to obtain a read-lock, then obtain itscurrent binary log coordinates and dump its data, before permitting the masterto continue executing statements. If you do not stop the execution ofstatements, the data dump and the master status information will not match,resulting in inconsistent or corrupted databases on the slaves. For moreinformation on replicating a MyISAM master, see Section 17.1.1.4, “Obtainingthe Replication Master Binary Log Coordinates”. If you are using InnoDB,you do not need a read-lock and a transaction that is long enough to transferthe data snapshot is sufficient. For more information, see Section 14.17, “InnoDB and MySQL Replication”.(ストレージエンジンの異なる操作、ロックテーブルなど)
· Configure the slave with settings for connecting to themaster, such as the host name, login credentials, and binary log file name andposition. (Slave構成接続マスターの情報)See Section 17.1.1.8, “Settingthe Master Configuration on the Slave”.
Note
Certain steps within the setup processrequire the SUPER privilege. If youdo not have this privilege, it might not be possible to enable replication.(権限が必要)
大拿demo:
It is possible to sync a database to a masterwhile the master is in active use. Commands starting "M:"are run onthe master, "S:"are run in the slave. Until the end, the slave stateis undefined. Thisworks for InnoDB tables, if you have set transactionality to REPEATABLE-READ.
M:begin;
M:flush tables with read lock;
M:show master status;
M:show databases;
M:(for each db:)
M:__show tables in $db;
M:__(for each table:)
M:____select 1 from $db.$table limit 1;
M:unlock tables;
M: settime_zone = '+00:00';
S: setforeign_key_checks = 0;
S:stop slave io_thread;
S:stop slave;
S:reset slave;
S:reset master;
S: settime_zone = '+00:00';
_:(for each database:)
S:__show databases like '$db';
_:__(if it does not exist:)
S:____create database $db;
_:__(for each table:)
M:____show create table $db.$table;
S:____show create table $db.$table;
_:____(if different:)
S:______drop table $db.$table;
_:____(if different or not on slave:)
S:______create table $db.$table .... ;
S:____delete from $db.$table;
M:____select * from $db.$table;
S:____insert into $db.$table values ([...from above...]);
S:__show tables in $db;
_:__(for each table that shouldn't be there:)
S:____drop table $db.$table;
S:show databases;
_:(for each db that shouldn't be there:)
S:__drop database $db;
S:change master to [...details from show master above...];
S:start slave io_thread;
S:start slave;
M:rollback;
(Urghh...I see no way to embed code into these posts. Hence the ugly "____"thing to indicate indentation. I suggest copying into something with anon-proportinal font, to make it easier to read)