MySQL主从(一):基本安装部署

需要部署一主一从,前面放置中间件kingshard，配置中间件之前,先配置好基础的主从环境

类目 属性 说明 系统 CentOS Linux release 7.2.1511 (Core) 无 ip-master 10.11.10.215 主数据库 ip-slave 10.11.10.216 从数据库 MySQL版本 5.7.20 无 同步模式 gtid 无

- 下载

[root@localhost ~]# mkdir -pv /data/download[root@localhost ~]# cd /data/download/[root@gzsd-mysql01-101110215 download]# wget "https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-5.7.19-17/binary/tarball/Percona-Server-5.7.19-17-Linux.x86_64.ssl101.tar.gz"[root@localhost mysql]# yum install libaio vim

采用的percona分支版本的考虑在于使用xtrabackup备份的时候，备份锁的粒度很小，而且有对应的线程池的功能。性能上比MySQL原本较为靠上(官方版本跟Percona性能测试不在今天这个范围之内)。
具体的配置过程

1.解压缩[root@gzsd-mysql01-101110215 download]# tar xvf Percona-Server-5.7.19-17-Linux.x86_64.ssl101.tar.gz -C /usr/local/2.新增用户[root@gzsd-mysql01-101110215 download]# groupadd mysql    [root@gzsd-mysql01-101110215 download]# useradd -r -g mysql -s /bin/false mysql[root@gzsd-mysql01-101110215 download]# cd /usr/local/[root@gzsd-mysql01-101110215 local]# ln -s Percona-Server-5.7.19-17-Linux.x86_64.ssl101 mysql[root@gzsd-mysql01-101110215 local]# mkdir -pv /data/mysql/mysql3306/{data,tmp,logs/iblog}[root@gzsd-mysql01-101110215 local]# chown -R mysql.mysql /data/mysql[root@gzsd-mysql01-101110215 local]# cd /usr/local/mysql/3.修改my.cnf文件见文章末尾4.初始化安装[root@gzsd-mysql01-101110215 mysql]# ./bin/mysqld --initialize --user=mysql#此时看error.log可以看到对应的的信息[root@gzsd-mysql01-101110215 ~]# tailf /data/mysql/mysql3306/logs/error.log 2017-11-27T15:16:42.919983+08:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set. 100 200 300 400 500 600 700 800 900 1000 100 200 300 400 500 600 700 800 900 10002017-11-27T15:16:58.822273+08:00 0 [Warning] InnoDB: New log files created, LSN=484332017-11-27T15:16:59.227121+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.2017-11-27T15:16:59.905396+08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f58d0e35-d342-11e7-a0c8-525400c86529.2017-11-27T15:16:59.963532+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2017-11-27T15:17:00.288153+08:00 0 [Warning] CA certificate ca.pem is self signed.**2017-11-27T15:17:00.305884+08:00 1 [Note] A temporary password is generated for root@localhost: N%DZ&21KgNxp**2017-11-27T15:17:21.693274+08:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693300+08:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693308+08:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693318+08:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693322+08:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693328+08:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693347+08:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.2017-11-27T15:17:21.693353+08:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

加强的为默认生成的随机密码,首次登陆后需要进行修改，复制启动脚本到/etc/init.d中

[root@gzsd-mysql01-101110215 mysql]# cp support-files/mysql.server /etc/init.d/mysqld[root@gzsd-mysql01-101110215 mysql]# chmod +x /etc/init.d/mysqld[root@gzsd-mysql01-101110215 mysql]# cp /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so /usr/lib64/[root@gzsd-mysql01-101110215 mysql]# vim /etc/init.d/mysqld basedir=/usr/local/mysqldatadir=/data/mysql/mysql3306/data#启动[root@gzsd-mysql01-101110215 mysql]# /etc/init.d/mysqld start

如果报错，请查看/data/mysql/mysql3306/logs/error.log
顺利启动之后，首先需要进行一个安全加固,加默认密码进行更改

[root@localhost mysql]# ln -s /data/mysql/mysql3306/tmp/mysql.sock /tmp/mysql.sock[root@gzsd-mysql01-101110215 mysql]# bin/mysql_secure_installation -S /data/mysql/mysql3306/tmp/mysql.sockmysql_secure_installation: [ERROR] unknown variable 'default-character-set=utf8'Securing the MySQL server deployment.Enter password for user root: 输入初始化密码The existing password for the user account root has expired. Please set a new password.New password: 输入新密码Re-enter new password: 确认密码VALIDATE PASSWORD PLUGIN can be used to test passwordsand improve security. It checks the strength of passwordand allows the users to set only those passwords which aresecure enough. Would you like to setup VALIDATE PASSWORD plugin?Press y|Y for Yes, any other key for No: y 是否使用增强的密码策略规则There are three levels of password validation policy:LOW    Length >= 8MEDIUM Length >= 8, numeric, mixed case, and special charactersSTRONG Length >= 8, numeric, mixed case, special characters and dictionary                  filePlease enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1Using existing password for root.Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : yNew password: 重新输入新密码Re-enter new password: 确认密码Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : yBy default, a MySQL installation has an anonymous user,allowing anyone to log into MySQL without having to havea user account created for them. This is intended only fortesting, and to make the installation go a bit smoother.You should remove them before moving into a productionenvironment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : y 是否移除匿名用户Success.Normally, root should only be allowed to connect from'localhost'. This ensures that someone cannot guess atthe root password from the network.Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y 禁止root远程登录Success.By default, MySQL comes with a database named 'test' thatanyone can access. This is also intended only for testing,and should be removed before moving into a productionenvironment.Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y 移除test数据库 - Dropping test database...Success. - Removing privileges on test database...Success.Reloading the privilege tables will ensure that all changesmade so far will take effect immediately.Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y 刷新权限列表Success.All done! 

将bin的路径添加到系统中

[root@gzsd-mysql01-101110215 mysql]# vim /etc/profileexport PATH=$PATH:/usr/local/mysql/bin[root@gzsd-mysql01-101110215 mysql]# source /etc/profile

至此，主库生成完毕，读库配置基本类同，不在累述,配置好读库后,进行主从配置

• 主库配置

root@(none) 04:07:21>show global variables like "%server%";+---------------------------------+--------------------------------------+| Variable_name                   | Value                                |+---------------------------------+--------------------------------------+| character_set_server            | utf8mb4                              || collation_server                | utf8mb4_general_ci                   || innodb_ft_server_stopword_table |                                      || server_id                       | 330610216                            || server_id_bits                  | 32                                   || server_uuid                     | b9cff64e-d348-11e7-913c-525400f96d7f |+---------------------------------+--------------------------------------+6 rows in set (0.00 sec)root@(none) 04:07:28>show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+------------------+----------+--------------+------------------+------------------------------------------+| mysql_bin.000003 |      194 |              |                  | b9cff64e-d348-11e7-913c-525400f96d7f:1-4 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)root@(none) 04:17:25>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl-oms-account'@'10.11.10.216' IDENTIFIED BY 'Hg;v13cB77]WNx';Query OK, 0 rows affected, 1 warning (0.35 sec)root@(none) 04:17:42>flush privileges;Query OK, 0 rows affected (0.02 sec)

可以看到主库执行到binlog的位置,对主库进行备份

[root@gzsd-mysql01-101110215 download]# mysqldump -uroot -p --single-transaction --master-data=2 -A >backup.sql[root@gzsd-mysql01-101110215 download]# scp backup.sql 10.11.10.216:/data/download/

• 读库配置

[root@localhost mysql]# cd /data/download/[root@localhost download]# mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock < backup.sql [root@localhost download]# mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock < backup.sql Enter password: ERROR 1840 (HY000) at line 33: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.[root@localhost download]# mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sockEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.19-17-log Percona Server (GPL), Release 17, Revision e19a6b7b73fCopyright (c) 2009-2017 Percona LLC and/or its affiliatesCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@(none) 04:22:07>reset master;Query OK, 0 rows affected (0.13 sec)[root@localhost download]# mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock < backup.sql Enter password: 

重新执行就不会报错了。

root@(none) 04:24:07>change master to master_host="10.11.10.215",master_port=3306,master_user='repl-oms-account',master_password='Hg;v13cB77]WNx',master_auto_position=1;root@(none) 04:26:03>start slave;

查看读库状态即可获知是否同步成功

root@(none) 04:27:00>show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.11.10.215                  Master_User: repl-oms-account                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000003          Read_Master_Log_Pos: 672               Relay_Log_File: relaylog.000002                Relay_Log_Pos: 414        Relay_Master_Log_File: mysql_bin.000003             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_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: 672              Relay_Log_Space: 614              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: 0Master_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: 330610215                  Master_UUID: f58d0e35-d342-11e7-a0c8-525400c86529             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates           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: f58d0e35-d342-11e7-a0c8-525400c86529:1-6                Auto_Position: 1         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

至此，基本的主从配置完毕。附录为my.cnf配置文件

[client]port=3306default-character-set=utf8socket=/data/mysql/mysql3306/tmp/mysql.sock[mysql]port=3306prompt=\\u@\\d \\r:\\m:\\s>default-character-set=utf8no-auto-rehashsocket=/data/mysql/mysql3306/tmp/mysql.sock[mysqld]port=3306sql_mode='NO_ENGINE_SUBSTITUTION'character-set-server=utf8mb4default-storage-engine=INNODBdefault-time-zone="+08:00"log_timestamps=SYSTEMexplicit_defaults_for_timestamp=1transaction-isolation = READ-COMMITTED# optimizer_switch='mrr_cost_based=off,batched_key_access=on'# directionaryinnodb_log_group_home_dir=/data/mysql/mysql3306/datainnodb_data_home_dir=/data/mysql/mysql3306/datainnodb_data_file_path=ibdata1:10M:autoextend:max:10Gdatadir=/data/mysql/mysql3306/datatmpdir=/data/mysql/mysql3306/tmp/slave_load_tmpdir=/data/mysql/mysql3306/tmp/log_error=/data/mysql/mysql3306/logs/error.logslow_query_log_file=/data/mysql/mysql3306/logs/slow.logsocket=/data/mysql/mysql3306/tmp/mysql.socklog_bin=/data/mysql/mysql3306/logs/mysql_binrelay_log=/data/mysql/mysql3306/logs/relaylog# undo settingsinnodb_undo_tablespaces=3innodb_undo_logs=128innodb_undo_directory=/data/mysql/mysql3306/logs/ibloginnodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128# innodb settingsinnodb_log_files_in_group=2innodb_log_file_size=1Ginnodb_flush_log_at_trx_commit=1innodb_max_dirty_pages_pct=50innodb_read_io_threads=8innodb_write_io_threads=16innodb_file_per_table=1innodb_thread_concurrency=16innodb_change_buffering=insertsinnodb_checksum_algorithm=crc32innodb_adaptive_flushing=1innodb_stats_on_metadata=0innodb_flush_method=O_DIRECTinnodb_log_buffer_size=10Minnodb_flush_neighbors=0innodb_lock_wait_timeout=50innodb_sync_array_size=16transaction-isolation = READ-COMMITTEDinnodb_open_files=65535open_files_limit=65535# tabletable_definition_cache=8192table_open_cache=8192table_open_cache_instances=16# thread and thread poolthread_stack=262144thread_cache_size=256thread_handling=pool-of-threadsthread_pool_high_prio_mode=transactionsthread_pool_idle_timeout=60thread_pool_oversubscribe=8thread_pool_size=32thread_pool_stall_limit=10# binlog and replicationbinlog_cache_size=5Mbinlog-format=ROWsync_binlog=1expire_logs_days=7log_slave_updates = 1relay_log_info_repository=TABLEmaster_info_repository=TABLErelay_log_recovery=1max_binlog_size=512Mbinlog-format=ROWslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'# semi sync replication settingsplugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 300rpl_semi_sync_slave_enabled = 1# new replication settingslave-parallel-type = LOGICAL_CLOCKslave-parallel-workers = 16slave_preserve_commit_order=1slave_transaction_retries=128# gtidgtid_mode=ONenforce_gtid_consistency=ONbinlog_gtid_simple_recovery = 1# slow loglog_slow_verbosity=fulllong_query_time=1slow_query_log=1skip-slave-start=1#comsquery_cache_type=0query_cache_size=0local-infile=1max_allowed_packet=64Mread_rnd_buffer_size=16Mjoin_buffer_size=8Msort_buffer_size=2Mread_buffer_size=4Mopen_files_limit=65535lower_case_table_names=1skip-name-resolve=1skip-external-locking=1safe-user-create#timeoutconnect_timeout=30delayed_insert_timeout =300innodb_rollback_on_timeout=OFFnet_read_timeout=30net_write_timeout=60slave_net_timeout=30#myisamconcurrent_insert=2key_buffer_size=8Mmyisam_sort_buffer_size=20M# START CONFIGUREserver_id=330610215max_connections=12000max_user_connections=10000max_connect_errors=35000innodb_buffer_pool_size=2Ginnodb_io_capacity=100innodb_io_capacity_max=400# END CONFIGURE[mysqld_safe]pid_file=/data/mysql/mysql3306/tmp/mysql.pidmalloc-lib=/usr/lib64/libjemalloc.sonuma-interleave=1