安裝mysql5.7 · TUNA-daily

[TOC] ## 1. 安裝mysql（5.7.22） 1. 注意防火墻（3306端口） 2. selinux配置 ### 1.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.56-2.el7.x86_64 --nodeps ~~~ ### 1.2 創建用戶 ~~~ groupadd mysql useradd -r -g mysql mysql ~~~ ### 1.3 解壓 ~~~ tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar ~~~ ### 1.4 安裝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.5 安裝server ~~~ rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm ~~~ ### 1.6 創建目錄 ~~~ mkdir -p /data/mysqldata chown mysql.mysql /data/mysqldata mkdir /var/log/mysql chown mysql.mysql /var/log/mysql ~~~ ### 1.7 修改主服務器配置 ~~~ vim /etc/my.cnf [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 ~~~ ### 1.7 初始化數據庫 ~~~ mysqld --initialize service mysqld start ~~~ ### 1.8 防火墻檢查 ~~~ firewall-cmd --state #防火墻列表 firewall-cmd --list-all #防火墻開放31005端口 firewall-cmd --permanent --add-port=3306/tcp # 防火墻重新加載配置 firewall-cmd --reload ~~~ ### 1.9 修改密碼 ~~~ grep 'temporary password' /var/log/mysql/error.log mysql -uroot -p'UA1zL8P9QzNJBcs1' SET PASSWORD = PASSWORD('4rfv$RFV'); show variables like 'datadir'; ~~~ ## 2. 主從搭建 ### 2.0 修改從服務器配置(從) ~~~ 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.1 重啟主、從服務器的mysql服務 ~~~ service mysql restart ~~~ ### 2.2 登錄主服務器mysql ~~~ GRANT REPLICATION SLAVE ON *.* TO 'systop'@'172.31.10.%' IDENTIFIED BY 'systop'; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 記下： file = mysqlmaster-bin.000001和 position = 329 ~~~ ### 2.3 導出主mysql數據 ~~~ mysqldump -uroot -p -P3306 --all-databases --triggers --routines --events >mysqlall.sql ~~~ ### 2.4 解鎖 ~~~ UNLOCK TABLES; ~~~ ### 2.5 將數據文件遠程拷貝到從服務器 ~~~ scp mysqlall.sql 192.168.X.X:/tmp/ ~~~ ### 2.6 從服務器導入數據 ~~~ mysql -uroot -p -h127.0.0.1 -P3306 < /tmp/mysqlall.sql ~~~ ### 2.7 從服務器執行 ~~~ 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.8 從服務器執行 ~~~ start slave; show slave status \G; 查看: Slave_IO_Running: Yes Slave_SQL_Running: Yes 都顯示yes表示啟動正常 ~~~ ## 錯誤 ### 1. ~~~ 2018-06-25T06:38:46.954958Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 2018-06-25T06:38:46.954977Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 2018-06-25T06:38:46.954982Z 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions 2018-06-25T06:38:46.954988Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2018-06-25T06:38:47.559229Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 2018-06-25T06:38:47.559331Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. ~~~ 解決辦法： ~~~ setenforce 0 vim /etc/selinux/config SELINUX=disabled ~~~