CentOS 7.2 mysql-5.7.17 源�a��g安�b操作、主主集群、数据库备份脚本

@H_403_0@MysqL tar 源�a��g安�b操作

@H_403_0@在下述的操作系统为CentOS 7.2，MysqL版本为MysqL-5.7.17

@H_403_0@

@H_403_0@1.安�b前�M行依�包安�b

@H_403_0@yum -y install gcc gcc-c++ ncurses ncurses-devel cmake bison bison-devel openssl openssl-devel pcre pcre-devel

@H_403_0@

@H_403_0@2.下载源码包

@H_403_0@2.1 建议到官方网站下载http://dev.MysqL.com/downloads/MysqL/，选择“Source Code”。下载的过程，需要注册一个Oracle账户，如果有直接登录就好

@H_403_0@

@H_403_0@2.2 提供一个下载地址：http://101.96.10.47/dev.MysqL.com/get/Downloads/MysqL-5.7/MysqL-boost-5.7.17.tar.gz

@H_403_0@或者https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz

@H_403_0@新版本的源码增加了boost库，如果你下的源码里面没有需要下载这个库，否则会报错，故需�M行下�d

@H_403_0@

@H_403_0@3.新建MysqL 用户和用户组

@H_403_0@groupadd -r MysqL

@H_403_0@useradd -r -g MysqL MysqL

@H_403_0@

@H_403_0@4.�M入安�b步�E

@H_403_0@tar -zvxf MysqL-boost-5.7.17.tar.gz

@H_403_0@cd MysqL-5.7.17

@H_403_0@

@H_403_0@新建MysqL 数据库数据文件目录

@H_403_0@mkdir -p /www/MysqL-data

@H_403_0@mkdir -p /www/MysqL-logs

@H_403_0@mkdir -p /www/MysqL-temp

@H_403_0@

@H_403_0@�M行�A��g

@H_403_0@cmake -DCMAKE_INSTALL_PREFIX=/www/MysqL \

@H_403_0@-DMysqL_DATADIR=/www/MysqL-data \

@H_403_0@-DMysqL_UNIX_ADDR=/www/MysqL/MysqL.sock \

@H_403_0@-DWITH_BOOST=/www/boost_1_59_0 \

@H_403_0@-DSYSCONFDIR=/etc \

@H_403_0@-DWITH_INNOBASE_STORAGE_ENGINE=1 \

@H_403_0@-DWITH_PARTITION_STORAGE_ENGINE=1 \

@H_403_0@-DWITH_FEDERATED_STORAGE_ENGINE=1 \

@H_403_0@-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

@H_403_0@-DWITH_MYISAM_STORAGE_ENGINE=1 \

@H_403_0@-DENABLED_LOCAL_INFILE=1 \

@H_403_0@-DENABLE_DTRACE=0 \

@H_403_0@-DDEFAULT_CHARSET=utf8mb4 \

@H_403_0@-DDEFAULT_COLLATION=utf8mb4_general_ci \

@H_403_0@-DWITH_EMBEDDED_SERVER=1

@H_403_0@

@H_403_0@上述配置解释：

@H_403_0@-DCMAKE_INSTALL_PREFIX=/www/MysqL //设置安装目录

@H_403_0@-DMysqL_DATADIR=/www/MysqL/data //设置数据库存放目录

@H_403_0@-DMysqL_UNIX_ADDR=/www/MysqL/MysqL.sock //设置UNIX socket目录

@H_403_0@-DDEFAULT_CHARSET=utf8mb4 //设置默认字符集

@H_403_0@-DDEFAULT_COLLATION=utf8mb4_general_ci //设置默认校对规则

@H_403_0@-DWITH_INNOBASE_STORAGE_ENGINE=1 //添加InnoDB引擎支持

@H_403_0@-DSYSCONFDIR=/etc //设置my.cnf配置文件的所在目录，默认为安装目录，�]有的�，後期可添加一份至此

@H_403_0@

@H_403_0@�M行��g安�b

@H_403_0@make && make install

@H_403_0@

@H_403_0@#安装完成后清理一下零时文件

@H_403_0@make clean

@H_403_0@

@H_403_0@设置启动脚本，开机自启动(即��MysqL做成服��)

@H_403_0@

@H_403_0@#拷贝可执行文件到指定的目录下，并修改名字为MysqLd

@H_403_0@cp /www/MysqL/support-files/MysqL.server /etc/init.d/MysqLd

@H_403_0@cp /www/MysqL/bin/MysqLd /usr/bin/MysqLd

@H_403_0@#授予可执行的权限

@H_403_0@chmod +x /etc/init.d/MysqLd

@H_403_0@#设置为开机启动

@H_403_0@systemctl enable MysqLd

@H_403_0@

@H_403_0@修改 MysqL的可执行目录

@H_403_0@chown -Rf MysqL:MysqL /www/MysqL

@H_403_0@chown -Rf MysqL:MysqL /www/MysqL-data

@H_403_0@chown -Rf MysqL:MysqL /www/MysqL-logs

@H_403_0@chown -Rf MysqL:MysqL /www/MysqL-temp

@H_403_0@

@H_403_0@配置文件即修改/etc/my.cnf

@H_403_0@#参考，具体里面的参数说明，请自行�W上搜索

@H_403_0@[MysqLd]

@H_403_0@character-set-server = utf8mb4

@H_403_0@collation-server = utf8mb4_general_ci

@H_403_0@skip-external-locking

@H_403_0@skip-name-resolve

@H_403_0@

@H_403_0@user = MysqL

@H_403_0@port = 3306

@H_403_0@

@H_403_0@basedir = /www/MysqL

@H_403_0@datadir = /www/MysqL-data

@H_403_0@tmpdir = /www/MysqL-temp

@H_403_0@# server_id = .....

@H_403_0@socket = /www/MysqL/MysqL.sock

@H_403_0@log-error = /www/MysqL-logs/MysqL_error.log

@H_403_0@pid-file = /www/MysqL-data/MysqL.pid

@H_403_0@

@H_403_0@open_files_limit = 10240

@H_403_0@

@H_403_0@back_log = 600

@H_403_0@max_connections=500

@H_403_0@max_connect_errors = 6000

@H_403_0@wait_timeout=605800

@H_403_0@

@H_403_0@#open_tables = 600

@H_403_0@#table_cache = 650

@H_403_0@#opened_tables = 630

@H_403_0@

@H_403_0@max_allowed_packet = 32M

@H_403_0@

@H_403_0@sort_buffer_size = 4M

@H_403_0@join_buffer_size = 4M

@H_403_0@thread_cache_size = 300

@H_403_0@query_cache_type = 1

@H_403_0@query_cache_size = 256M

@H_403_0@query_cache_limit = 2M

@H_403_0@query_cache_min_res_unit = 16k

@H_403_0@

@H_403_0@tmp_table_size = 256M

@H_403_0@max_heap_table_size = 256M

@H_403_0@

@H_403_0@key_buffer_size = 256M

@H_403_0@read_buffer_size = 1M

@H_403_0@read_rnd_buffer_size = 16M

@H_403_0@bulk_insert_buffer_size = 64M

@H_403_0@

@H_403_0@lower_case_table_names=1(不�^分大小��O定)

@H_403_0@

@H_403_0@default-storage-engine = INNODB

@H_403_0@

@H_403_0@innodb_buffer_pool_size = 1G

@H_403_0@innodb_log_buffer_size = 32M

@H_403_0@innodb_log_file_size = 128M

@H_403_0@innodb_flush_method = O_DIRECT

@H_403_0@

@H_403_0@#####################

@H_403_0@long_query_time= 2

@H_403_0@slow-query-log = on

@H_403_0@slow-query-log-file = /www/MysqL-logs/MysqL-slow.log

@H_403_0@

@H_403_0@[MysqLdump]

@H_403_0@quick

@H_403_0@max_allowed_packet = 32M

@H_403_0@character_set_server = utf8

@H_403_0@

@H_403_0@[MysqLd_safe]

@H_403_0@log-error=/var/log/MysqLd.log

@H_403_0@pid-file=/var/run/MysqLd/MysqLd.pid

@H_403_0@character_set_server = utf8

@H_403_0@

@H_403_0@�湓]：上述character_set_server = utf8�凫督�Q�y�a��}

@H_403_0@

@H_403_0@添加环境变量 vi /etc/profile

@H_403_0@

@H_403_0@#末尾添加以下内容

@H_403_0@#MysqL env

@H_403_0@export PATH=$PATH:/www/MysqL/bin:/www/MysqL/lib

@H_403_0@

@H_403_0@使得�O置文件生效 source /etc/profile

@H_403_0@

@H_403_0@初始化数据库

@H_403_0@MysqLd --initialize-insecure --user=MysqL --basedir=/www/MysqL --datadir=/www/MysqL-data

@H_403_0@

@H_403_0@备注：

@H_403_0@MysqL之前版本MysqL_install_db是在MysqL_basedir/script下

@H_403_0@MysqL 5.7直接放在了MysqL_install_db/bin目录下。

@H_403_0@"�Cinitialize"已废弃,生成一个随机密码(~/.MysqL_secret)

@H_403_0@"�Cinitialize-insecure"不会生成密码

@H_403_0@"�Cdatadir"目录下不能有数据文件

@H_403_0@

@H_403_0@或者使用下列�Z句：

@H_403_0@MysqL_install_db --user=MysqL --basedir=/www/MysqL --datadir=/www/MysqL-data

@H_403_0@

@H_403_0@启动数据库

@H_403_0@systemctl start MysqLd

@H_403_0@

@H_403_0@查看��B

@H_403_0@systemctl status MysqLd

@H_403_0@

@H_403_0@查看MysqL服务进程和端口

@H_403_0@ps -ef | grep MysqL

@H_403_0@netstat -tunpl | grep 3306

@H_403_0@

@H_403_0@设置数据库root用户密码

@H_403_0@MysqL和Oracle数据库一样，数据库也默认自带了一个root用户（这个和当前Linux主机上的

@H_403_0@root用户是完全不搭边的），我们在设置好MysqL 数据库的安全配置后初始化root用户的密码。

@H_403_0@配制过程中，一路输入 y 就行了。这里只说明下MysqL5.7.17版本中，用户密码策略分成低级

@H_403_0@LOW 、中等 MEDIUM 和超强 STRONG 三种，推荐使用中等 MEDIUM 级别！��然也可以不�M行等

@H_403_0@��O置

@H_403_0@

@H_403_0@查阅官方文档后发现有以下三种密码策略：

@H_403_0@政策测试执行

@H_403_0@0 ---LOW 长度

@H_403_0@1 ---MEDIUM 长度; 数字，小写/大写和特殊字符

@H_403_0@2 ---STRONG 长度; 数字，小写/大写和特殊字符; 字典文件

@H_403_0@

@H_403_0@�湓]：後�m的用�粝嚓P密�a�⑿枰�按照此��绦校�但是也可以�M行相�P修改

@H_403_0@

@H_403_0@MysqL_secure_installation

@H_403_0@

@H_403_0@除了

@H_403_0@Disallow root login remotely

@H_403_0@Remove test database and accesss to it 可以��n，

@H_403_0@其他都是y

@H_403_0@

@H_403_0@�M入��

@H_403_0@MysqL -uroot -p密�a

@H_403_0@

@H_403_0@��建其他用��

@H_403_0@MysqL> create user 'hua'@'localhost' identified by 'hlj123';(後�m可以修改)

@H_403_0@

@H_403_0@�O置存�σ�擎

@H_403_0@MysqL> set storage_engine=INNODB;

@H_403_0@

@H_403_0@��建��

@H_403_0@MysqL> create database zgz character set utf8;

@H_403_0@查看是否建立成功

@H_403_0@MysqL> show databases;

@H_403_0@�x予用�粝嚓P��嘞�

@H_403_0@MysqL> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.134.100.174' IDENTIFIED BY 'FLM@88' WITH GRANT OPTION;

@H_403_0@MysqL> flush privileges;

@H_403_0@

@H_403_0@�z�是否�x予�h程�B接��嘞抟验_通

@H_403_0@MysqL -h 10.134.100.174 -uroot -pFLM@88 zgz

@H_403_0@如果�M入��煺f明成功，否�t重��MysqL服�占纯稍俅�L�

@H_403_0@若是允�S�用�敉ㄟ^任意服�掌骺蛇M行�B接相�P��

@H_403_0@MysqL> GRANT ALL PRIVILEGES ON databasename.tables TO 'root'@'%' IDENTIFIED BY 'Foxconn@88' WITH GRANT OPTION;

@H_403_0@MysqL> flush privileges;

@H_403_0@

@H_403_0@�С瞿阈枰�的��

@H_403_0@MysqLdump -h 10.157.136.134 -uroot -proot --events --ignore-table=MysqL.event --default-character-set=UTF8 jspxcms >zgz.sql

@H_403_0@

@H_403_0@提前建立一��空��泶娣判枰��入的��，後只需要�绦��入命令

@H_403_0@MysqL> use zgz;

@H_403_0@MysqL> source /root/zgz.sql(此��槟惴胖�zgz.sql的存放位置)

@H_403_0@

@H_403_0@确定数据表是否创建成功，即数据文件是否导入成功

@H_403_0@MysqL> show tables;

@H_403_0@

@H_403_0@��你需要查看��前密�a策略，可�M行下列操作：

@H_403_0@MysqL> show VARIABLES like "%password%"

@H_403_0@+---------------------------------------+---------+

@H_403_0@| Variable_name | Value |

@H_403_0@|---------------------------------------+---------|

@H_403_0@| default_password_lifetime | 0 |

@H_403_0@| disconnect_on_expired_password | ON |

@H_403_0@| log_builtin_as_identified_by_password | OFF |

@H_403_0@| MysqL_native_password_proxy_users | OFF |

@H_403_0@| old_passwords | 0 |

@H_403_0@| report_password | |

@H_403_0@| sha256_password_proxy_users | OFF |

@H_403_0@| validate_password_dictionary_file | |

@H_403_0@| validate_password_length | 8 |

@H_403_0@| validate_password_mixed_case_count | 1 |

@H_403_0@| validate_password_number_count | 1 |

@H_403_0@| validate_password_policy | MEDIUM |

@H_403_0@| validate_password_special_char_count | 1 |

@H_403_0@+---------------------------------------+---------+

@H_403_0@13 rows in set

@H_403_0@Time: 0.030s

@H_403_0@

@H_403_0@修改默认密码策略（当然实际环境是不推荐修改为更低安全策略的）

@H_403_0@MysqL> set global validate_password_policy = 0;

@H_403_0@

@H_403_0@validate_password_number_count指定了密码中数据的长度，

@H_403_0@validate_password_special_char_count指定了密码中特殊字符的长度，

@H_403_0@validate_password_mixed_case_count指定了密码中大小字母的长度。

@H_403_0@

@H_403_0@这些参数，默认值均为1，所以validate_password_length最小值为4，

@H_403_0@如果你显示指定validate_password_length的值小于4，尽管不会报错，

@H_403_0@但validate_password_length的值将设为4。

@H_403_0@

@H_403_0@修改validate_password_number_count，validate_password_special_char_count，

@H_403_0@validate_password_mixed_case_count中任何一个值，则validate_password_length将进行动态修改

@H_403_0@

@H_403_0@另，可通过my.cnf配置文件设置密码策略的级别

@H_403_0@vi /etc/my.cnf

@H_403_0@[MysqLd]

@H_403_0@validate_password_policy=2

@H_403_0@validate_password_policy设置MysqL启动的时候，密码策略级别如果设置为3，那么需要指定字典文件

@H_403_0@

@H_403_0@当然你也可以通过my.cnf配置文件关闭validate_password插件

@H_403_0@只需要添加一行

@H_403_0@validate_password = off

@H_403_0@编辑完配置文件后，重启MysqLd服务即可生效。

@H_403_0@MysqL > show VARIABLES like "validate_password%"

@H_403_0@+-----------------+---------+

@H_403_0@| Variable_name | Value |

@H_403_0@|-----------------+---------|

@H_403_0@+-----------------+---------+

@H_403_0@0 rows in set

@H_403_0@Time: 0.008s

@H_403_0@关闭validate_password插件后，就没有了validate_password的一些参数变量。

@H_403_0@

@H_403_0@MysqL新版本默认监听在IPv6的地址族上。更改为监听IPv4地址族，

@H_403_0@修改my.cnf添加一行配置：bind-address = 0.0.0.0

@H_403_0@重启MysqLd即可

@H_403_0@

@H_403_0@MysqL主主�}�u配置

@H_403_0@首先分�e在�商ǚ��掌魃习惭b上述的安�bMysqL的步�E，�_保有�商ǚ��掌骺墒褂�

@H_403_0@故��旆�务器环境：

@H_403_0@+-------------------+---------------------------------------------------

@H_403_0@| |服务器节点1 |服务器节点2 |

@H_403_0@+-------------------+------------------------+-------------------------+

@H_403_0@|OS |CentOS 7.2 |CentOS 7.2 |

@H_403_0@+-------------------+------------------------+-------------------------+

@H_403_0@|DB Version |MysqL 5.7.17 |MysqL 5.7.17 |

@H_403_0@+-------------------+------------------------+-------------------------+

@H_403_0@|HostName |Linux01 |Linux02 |

@H_403_0@+-------------------+------------------------+-------------------------+

@H_403_0@|IPADDR |172.16.100.69 |172.16.100.70 |

@H_403_0@+-------------------+------------------------+-------------------------+

@H_403_0@

@H_403_0@创建复制需要的用户，用户需要slave权限（必要），file，select权限（可选）

@H_403_0@MysqL> grant replication slave,file,select on *.* to 'MysqL_sync'@'172.16.100.69' identified by 'FLM88'

@H_403_0@MysqL> grant replication slave,select on *.* to 'MysqL_sync'@'172.16.100.70' identified by 'FLM88'

@H_403_0@

@H_403_0@备注：如果想要在Slave上有权限执行 "LOAD TABLE FROM MASTER" 或

@H_403_0@"LOAD DATA FROM MASTER" 语句的话，必须授予全局的 FILE 和 SELECT 权限。

@H_403_0@上述的�Z句就是�M行了��建了同步��煊��MysqL_sync,密�a及相�P�嘞薜脑O定

@H_403_0@

@H_403_0@分别修改两台服务器上面的配置文件vi /etc/my.cnf

@H_403_0@172.16.100.69上的my.cnf：

@H_403_0@log-bin=MysqL-bin-db01

@H_403_0@server-id=11

@H_403_0@log-slave-updates

@H_403_0@slave-skip-errors=all

@H_403_0@auto_increment_increment=2

@H_403_0@auto_increment_offset=1

@H_403_0@

@H_403_0@172.16.100.70上的my.cnf：

@H_403_0@log-bin=MysqL-bin-db02

@H_403_0@server-id=12

@H_403_0@log-slave-updates

@H_403_0@slave-skip-errors=all

@H_403_0@auto_increment_increment=2

@H_403_0@auto_increment_offset=2

@H_403_0@

@H_403_0@�湓]：server-id两个服务器必须配置不一样，

@H_403_0@auto_increment_increment配置每个节点中的自动增长字段每次增长的步长

@H_403_0@auto_increment_offset配置各个节点自动增长字段的初始值从多少开始

@H_403_0@如果我们不设置这两个变量的话，含有自动增长字段的表将在多个服务器上面各自维护，会出现重复的情况导致复制出错。

@H_403_0@如上面的设置，A节点自增长的字段产生的值将会是 1,3,5....

@H_403_0@而B节点将是2,4,6......

@H_403_0@

@H_403_0@重启两台数据库实例

@H_403_0@systemctl restart MysqLd

@H_403_0@

@H_403_0@在两个数据库中执行：show master status;查看主服务器状态：

@H_403_0@172.16.100.69(��c1):

@H_403_0@MysqL> show master status;