您好,登錄后才能下訂單哦!
三臺(tái)服務(wù)器:主機(jī)192.168.11.126,192.168.11.127,從:192.168.11.128
準(zhǔn)備同步的庫(kù):192.168.11.126 ,account_db,192.168.11.127,game_db;
首先在各自服務(wù)器上面安裝mysql,在從機(jī)上面用mysqld_multi安裝2個(gè)數(shù)據(jù)庫(kù),3307,3308
3307對(duì)應(yīng)192.168.11.126,3308對(duì)應(yīng)192.168.11.127;
1,建立好目錄:
mkdir -p /data/mysql{3307,3308}
mkdir -p /data/mysql{3307,3308}/data
mkdir -p /data/mysql{3307,3308}/binlog
mkdir -p /data/mysql{3307,3308}/relay_log
chown -R mysql:mysql /data/mysql{3307,3308}
chown -R mysql:mysql /data/mysql{3307,3308}/data
vim /etc/my.cnf
添加:
[mysqld_multi]
mysqld=/data/mysql/bin/mysqld_safe
mysqladmin=/data/mysql/bin/mysqladmin
log=/data/mysql/mydata/log/mysqld_multi.log
[mysqld1]
port= 3307
socket= /data/mysql3307/mysql.sock
datadir = /data/mysql3307/data
server-id = 1231
expire_logs_days = 2
log-bin = /data/mysql3307/mysqllog/binlog/mysql-bin
replicate-do-db=account_db
replicate-ignore-db=mysql
relay_log =/data/mysql3307/relay_log/mysql-relay-bin
log_slave_updates = 1
character_set_server = utf8
sql_mode = NO_AUTO_CREATE_USER
read_only = 0
wait_timeout = 64800
interactive_timeout = 64800
skip-name-resolve
#default-character-set = utf8
lower_case_table_names = 1
/data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql3307/data
--user –basedir分開(kāi)要不會(huì)報(bào)錯(cuò);
會(huì)跳出下面信息:
/data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql3307/data
WARNING: The host 'iZbp11h60sm7xheqt4hlh2Z' could not be looked up with /data/mysql/bin/resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...2017-07-21 16:21:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-07-21 16:21:10 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2017-07-21 16:21:10 0 [Note] /data/mysql/bin/mysqld (mysqld 5.6.34) starting as process 24280 ...
2017-07-21 16:21:10 24280 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-07-21 16:21:10 24280 [Note] InnoDB: The InnoDB memory heap is disabled
2017-07-21 16:21:10 24280 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
.......
2017-07-21 16:21:12 24280 [Note] RSA private key file not found: /data/mysql3307/data//private_key.pem. Some authentication plugins will not work.
2017-07-21 16:21:12 24280 [Note] RSA public key file not found: /data/mysql3307/data//public_key.pem. Some authentication plugins will not work.
2017-07-21 16:21:12 24280 [Note] Binlog end
2017-07-21 16:21:12 24280 [Note] InnoDB: FTS optimize thread exiting.
2017-07-21 16:21:12 24280 [Note] InnoDB: Starting shutdown...
2017-07-21 16:21:13 24280 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
2017-07-21 16:21:13 24302 [Note] RSA public key file not found: /data/mysql3307/data//public_key.pem. Some authentication plugins will not work.
2017-07-21 16:21:13 24302 [Note] Binlog end
2017-07-21 16:21:13 24302 [Note] InnoDB: FTS optimize thread exiting.
2017-07-21 16:21:13 24302 [Note] InnoDB: Starting shutdown...
2017-07-21 16:21:15 24302 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
.........
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
同樣準(zhǔn)備3308端口的數(shù)據(jù)庫(kù)及初始化及配置my.cnf端口不同,同步數(shù)據(jù)庫(kù)名字修改:
/data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql3308/data
啟動(dòng):
mysqld_multi --defaults-extra-file=/etc/my.cnf start 1,2
mysqld_multi --defaults-extra-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
停止:
mysqld_multi --defaults-extra-file=/etc/my.cnf stop 1,2
單個(gè)進(jìn)入mysql:
mysql --socket=/data/mysql3307/mysql.sock
mysql --socket=/data/mysql3307/mysql.sock -uroot -p
mysql --socket=/data/mysql3308/mysql.sock
[root@iZbp11h60sm7xheqt4hlh2Z data]# mysql --socket=/data/mysql3308/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, 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>
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3308 |
+--------+
1 row in set (0.00 sec)
mysql --socket=/data/mysql3308/mysql.sock -uroot -p
use game_db;
source /home/game_db.sql;
select * from user where user ='root' \G;
update user set host="192.168.11.%" where host='127.0.0.1';
update user set password=password('123456')where user='root';
主庫(kù)給rep權(quán)限:
grant replication slave,file on *.* to 'replicate'@'192.168.11.128' identified by 'rep@123';
主庫(kù)上面添加字段:
server-id=1 #設(shè)置服務(wù)器id,為1表示主服務(wù)器,注意:如果原來(lái)的配置文件中已經(jīng)有這一行,就不用再添加了。
log_bin=mysql-bin #啟動(dòng)MySQ二進(jìn)制日志系統(tǒng),注意:如果原來(lái)的配置文件中已經(jīng)有這一行,就不用再添加了。
binlog-do-db=game_db #需要同步的數(shù)據(jù)庫(kù)名,如果有多個(gè)數(shù)據(jù)庫(kù),可重復(fù)此參數(shù),每個(gè)數(shù)據(jù)庫(kù)一行
binlog-ignore-db=mysql #不同步mysql系統(tǒng)數(shù)據(jù)庫(kù)
service mysqld restart #重啟MySQL
mysql -u root -p #進(jìn)入mysql控制臺(tái)
show master status; 查看主服務(wù)器,出現(xiàn)以下類(lèi)似信息拿192.168.11.127game_db來(lái)說(shuō)吧:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 120 | game_db | mysql |
+------------------+----------+--------------+------------------+
配置MySQL從服務(wù)器的my.cnf文件
vi /etc/my.cnf #編輯配置文件,在[mysqld]部分添加下面內(nèi)容
server-id=2 #配置文件中已經(jīng)有一行server-id=1,修改其值為2,表示為從數(shù)據(jù)庫(kù)
log-bin=mysql-bin #啟動(dòng)MySQ二進(jìn)制日志系統(tǒng),注意:如果原來(lái)的配置文件中已經(jīng)有這一行,就不用再添加了。
replicate-do-db=game_db#需要同步的數(shù)據(jù)庫(kù)名,如果有多個(gè)數(shù)據(jù)庫(kù),可重復(fù)此參數(shù),每個(gè)數(shù)據(jù)庫(kù)一行
replicate-ignore-db=mysql #不同步mysql系統(tǒng)數(shù)據(jù)庫(kù)
配置以后重啟mysql3308;
stop slave;
change master to master_host='192.168.11.127',master_user='replicate',master_password='rep@123',master_log_file='mysql-bin.000002',master_log_pos=120;
start slave
show slave status \G;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.127
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2234
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 2069
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: game_db,game_db
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2234
Relay_Log_Space: 2242
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 84631394-7036-11e7-b3e9-000c29b53e0b
Master_Info_File: /data/mysql3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
note:mysql端口不是默認(rèn)3306的話(huà)在my.cnf修改。從機(jī)記得加上master_port=port
change master to master_host='192.168.11.127',master_user='replicate',master_port=3306,master_password='rep@123',master_log_file='mysql-bin.000002',master_log_pos=120;
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。