[TOC] ## 查看可用配置文件 ## 版本 系統：Red Hat Enterprise Linux Server release 7.3 (Maipo) mysql版本：5.6.39 ~~~ /usr/bin/mysql --verbose --help | grep -A 1 'Default options' ~~~ ## 1. 安裝mysql（5.6.38） 1. 安裝mysql 首先查看是否存在mysql ~~~ rpm -qa | grep -i mysql yum -y remove mysql-libs* ~~~ 依賴： ~~~ yum -y install net-tools autoconf rpm -e mariadb-libs-1:5.5.52-1.el7.x86_64 --nodeps ~~~ ### 1.1 創建用戶 ~~~ groupadd mysql useradd -r -g mysql mysql ~~~ ### 1.2 解壓 ~~~ tar -xvf MySQL-5.6.39-1.el7.x86_64.rpm-bundle.tar ~~~ ### 1.3 安裝server ~~~ rpm -ivh MySQL-server-5.6.39-1.el7.x86_64.rpm ~~~ ### 1.4 安裝client ~~~ rpm -ivh MySQL-devel-5.6.39-1.el7.x86_64.rpm rpm -ivh MySQL-client-5.6.39-1.el7.x86_64.rpm ~~~ ### 1.5 修改配置文件 ~~~ cp /usr/share/mysql/my-default.cnf /etc/my.cnf ~~~ ### 1.6 初始化數據庫 直接將/var/lib/mysql/目錄下的mysql數據復制過去即可，不用重新初始化數據到/data/mysqldata目錄 ~~~ mkdir -p /data/mysqldata chown mysql.mysql /data/mysqldata service mysql start ~~~ ### 1.7 修改密碼 ~~~ cat /root/.mysql_secret mysql -uroot -p'UA1zL8P9QzNJBcs1' SET PASSWORD = PASSWORD('4rfv$RFV'); ~~~ ## 2. 主從搭建 ~~~ cp -r /var/lib/mysql/* /data/mysqldata/ chown -R mysql.mysql /data/mysqldata mkdir /var/log/mysql chown mysql.mysql /var/log/mysql setenforce 0 vim /etc/sysconfig/selinux 把SELINUX=enforcing改為SELINUX=disabled ~~~ ### 2.1 修改主服務器配置 ~~~ vim /etc/my.cnf 在【mysqld】添加 [client] port = 3306 socket = /data/mysqldata/mysqld.sock default-character-set = utf8 [mysqld_safe] socket =/data/mysqldata/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /data/mysqldata/mysqld.pid socket = /data/mysqldata/mysqld.sock port = 3306 basedir = /usr datadir = /data/mysqldata tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 200 query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M character-set-server=utf8 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log sync_binlog=1 binlog_format=mixed lower_case_table_names = 1 innodb_buffer_pool_size=1G [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] default-character-set=utf8 [isamchk] key_buffer = 16M ~~~ 重啟mysql ### 2.2 修改從服務器配置 ~~~ vim /etc/my.cnf 在【mysqld】添加 [client] port = 3306 socket = /data/mysqldata/mysqld.sock default-character-set = utf8 [mysqld_safe] socket = /data/mysqldata/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /data/mysqldata/mysqld.pid socket = /data/mysqldata/mysqld.sock port = 3306 basedir = /usr datadir = /data/mysqldata tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 200 query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M character-set-server=utf8 lower_case_table_names = 1 server-id=2 log-bin=/var/log/mysql/mysql-bin.log innodb_buffer_pool_size=1G [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] default-character-set=utf8 [isamchk] key_buffer = 16M ~~~ ### 2.3 重啟主、從服務器的mysql服務 ~~~ service mysql restart ~~~ 不行的話就（修改了pid位置） ~~~ ps -ef|grep mysql kill -9 進程號 ~~~ ### 2.4登錄主服務器mysql ~~~ GRANT REPLICATION SLAVE ON *.* TO 'systop'@'192.168.%' IDENTIFIED BY 'systop'; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 記下： file = mysqlmaster-bin.000001和 position = 329 ~~~ ### 2.5 導出主mysql數據 ~~~ mysqldump -uroot -p -P3306 --all-databases --triggers --routines --events >mysqlall.sql ~~~ ### 2.6 解鎖 ~~~ UNLOCK TABLES; ~~~ ### 2.7 將數據文件遠程拷貝到從服務器 ~~~ scp mysqlall.sql 192.168.X.X:/tmp/ ~~~ ### 2.8 從服務器導入數據 ~~~ mysql -uroot -p -h127.0.0.1 -P3306 < /tmp/mysqlall.sql ~~~ ### 2.9 從服務器執行 ~~~ CHANGE MASTER TO MASTER_HOST='192.168.4.101', MASTER_USER='systop',MASTER_PASSWORD='systop',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=120; ~~~ ### 2.10 從服務器執行 ~~~ start slave; show slave status \G; 查看: Slave_IO_Running: Yes Slave_SQL_Running: Yes 都顯示yes表示啟動正常 ~~~ **主意，如果Slave_IO_Running一直顯示Connecting，則很有可能是防火墻的原因** ~~~ firewall-cmd --permanent --add-port=3306/tcp firewall-cmd --reload ~~~ ## 安裝mysql（5.7.22） 1. 安裝mysql 首先查看是否存在mysql ~~~ rpm -qa | grep -i mysql yum -y remove mysql-libs* ~~~ ~~~ rpm -qa|grep mariadb rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps ~~~ 依賴： ~~~ yum -y install net-tools autoconf ~~~ 1.1 創建用戶 ~~~ groupadd mysql useradd -r -g mysql mysql ~~~ 1.2 解壓 ~~~ tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar ~~~ 1.3 安裝client ~~~ rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm rpm -ivh mysql-community-devel-5.7.22-1.el7.x86_64.rpm rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm ~~~ 1.4 安裝server ~~~ rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm ~~~ 1.5 修改配置 修改MySQL服務器配置 ~~~ mkdir -p /var/log/mysql chown -R mysql.mysql /var/log/mysql mkdir -p /usr/share/mysql chown -R mysql.mysql /usr/share/mysql mkdir -p /var/log/mysql chown -R mysql.mysql /var/log/mysql ~~~ ~~~ vim /etc/my.cnf [client] default-character-set = utf8 [mysqld_safe] nice = 0 [mysqld] user = mysql port = 3306 tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 max_connections = 200 query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M character-set-server=utf8 server-id = 1 log_bin = /var/lib/mysql/mysql-bin.log sync_binlog=1 binlog_format=mixed lower_case_table_names = 1 innodb_buffer_pool_size=1G [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] default-character-set=utf8 ~~~ 重啟mysql 1.6 初始化數據庫 ~~~ mysqld --initialize service mysqld start ~~~ 1.7 修改密碼 ~~~ grep 'temporary password' /var/log/mysqld.log mysql -uroot -p'UA1zL8P9QzNJBcs1' SET PASSWORD = PASSWORD('4rfv$RFV'); ~~~