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 'iZbp11h50sm7xheqt4hlh1Z' 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@iZbp11h50sm7xheqt4hlh1Z data]# mysql --socket=/data/mysql3308/mysql.sock
　　Welcome to the MySQL monitor.  Commands end with ; or \g.

　　Your MySQL connection>　　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'>　　主库上面添加字段：
　　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>　　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;