mysql兩主一從配置

三臺服務器：主機192.168.11.126,192.168.11.127,從：192.168.11.128

準備同步的庫：192.168.11.126 ，account_db,192.168.11.127，game_db;

首先在各自服務器上面安裝mysql，在從機上面用mysqld_multi安裝2個數據庫，3307,3308

3307對應192.168.11.126，3308對應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分開要不會報錯；

會跳出下面信息：

/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

同樣準備3308端口的數據庫及初始化及配置my.cnf端口不同，同步數據庫名字修改：

/data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql3308/data

啟動：

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

單個進入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';

主庫給rep權限：

grant replication slave,file on *.* to 'replicate'@'192.168.11.128' identified by 'rep@123';

主庫上面添加字段：

server-id=1   #設置服務器id，為1表示主服務器，注意：如果原來的配置文件中已經有這一行，就不用再添加了。
log_bin=mysql-bin  #啟動MySQ二進制日志系統，注意：如果原來的配置文件中已經有這一行，就不用再添加了。
binlog-do-db=game_db  #需要同步的數據庫名，如果有多個數據庫，可重復此參數，每個數據庫一行
binlog-ignore-db=mysql   #不同步mysql系統數據庫
service mysqld  restart  #重啟MySQL
mysql -u root -p   #進入mysql控制臺
show master status;  查看主服務器，出現以下類似信息拿192.168.11.127game_db來說吧:

+------------------+----------+--------------+------------------+
| File                        | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |    120    | game_db    | mysql                  |
+------------------+----------+--------------+------------------+

配置MySQL從服務器的my.cnf文件
vi /etc/my.cnf   #編輯配置文件，在[mysqld]部分添加下面內容
server-id=2   #配置文件中已經有一行server-id=1，修改其值為2，表示為從數據庫
log-bin=mysql-bin  #啟動MySQ二進制日志系統，注意：如果原來的配置文件中已經有這一行，就不用再添加了。
replicate-do-db=game_db#需要同步的數據庫名，如果有多個數據庫，可重復此參數，每個數據庫一行
replicate-ignore-db=mysql   #不同步mysql系統數據庫

配置以后重啟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端口不是默認3306的話在my.cnf修改。從機記得加上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;