生產環境Linux二進制安裝配置MySQL

生產環境Linux二進制安裝配置MySQL

一、環境檢查
rpm -qa |grep libaio #如果沒有安裝libaio則需要安裝。
df -h
mount /dev/sr0 /mnt
二、安裝過程
1）上傳安裝程序
通過FTP/SFTP上傳安裝包
2）創建創用戶,組，創建目錄
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir -p /mysql/data/3306/data
這里的3306是MySQL默認端口號，/mysql/data目錄mysql的安裝目錄,
/mysql/data/3306/data是mysql數據目錄。
mkdir -p /mysql/log/3306

3）解壓安裝程序包
cd /mysql/app
tar zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
ln -s /mysql/app/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz /mysql/app/mysql
4）修改權限
chown -R mysql:mysql /mysql

5）配置環境變量
vi ~/.bash_profile
PATH=$PATH:/mysql/app/mysql/bin:$HOME/bin

測試
[root@db1 ~]# mysql --version
卸載系統自帶的mysql
yum remove mysql
[root@db1 ~]# which mysql
/mysql/app/mysql/bin/mysql
6）準備參數文件
mysql 默認讀取此路徑的/etc/my.cnf配置文件.
cd /mysql/data/3306/
touch /mysql/log/3306/itpuxdb-error.err
chown -R mysql:mysql /mysql/log/3306/itpuxdb-error.err

vi /mysql/data/3306//my.cnf

[client]
port=3306
socket = /mysql/data/3306/mysql.sock

[mysql]
no-beep
prompt="\u@db1 \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8

[mysqld]
########basic settings########
server-id=3306
port=3306
user = mysql
bind_address= 192.168.1.51
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket= /mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/db1.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/db1.err
long_query_time=10
log-error=/mysql/log/3306/db1-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
#log_bin = "/log/bin_log/binlog"

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

########innodb settings########

innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

7）初始化MySQL
mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --datadir=/mysql/data/3306/data --basedir=/mysql/app/mysql --user=mysql

cd /mysql/log/3306
more db1-error.err
初始化的隨機密碼
ifyicOu0au+y
8）配置啟動腳本
cd /mysql/app/mysql/support-files
cp mysql.server mysql

vi mysql
第一處要改：
#把前面所有出現的basedir,datadir,bindir,sbindir修改
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
bindir=/mysql/app/mysql/bin
sbindir=/mysql/app/mysql/bin
libexecdir=/mysql/app/mysql/bin

第二處要改：
mysqld_pid_file_path=/mysql/data/3306/mysql.pid

第三處要改：
#啟動腳本的start 項，添加--defaults-file=/mysql/data/3306/my.cnf
$bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &

my.cnf的檢查順序
[root@itpux support-files]# mysql --help|grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

#把/etc/my.cnf重命名

最后把修改好的mysql腳本放到/etc/init.d/目錄下。

mv /mysql/app/mysql/support-files/mysql /etc/init.d/

9）啟停驗證，日志查看
啟動
/etc/init.d/mysql start 或 service mysql start
停止
/etc/init.d/mysql stop 或 service mysql stop

10）后期配置（改密碼，啟動遠程訪問）
登錄
mysql -uroot -pxjl61l/J_tp9 -S /mysql/data/3306/mysql.sock
修改密碼
set password = password("rootroot");
11）遠程連接測試
root用戶實現遠程登錄
grant all privileges on . to 'root'@'%' identified by 'rootroot' with grant option;
flush privileges;
創建數據庫
create database db1
創建用戶
create user dbuser identified by "dbuser";
mysql> grant all privileges on dbuser. to 'dbuser'@'localhost' identified by 'dbuser';
mysql> grant all privileges on . to 'dbuser'@'%' identified by 'dbuser' with grant option;
mysql> grant all privileges on dbuser. to 'dbuser'@'%' identified by 'dbuser';
mysql> flush privileges;

創建表和添加測試數據
use db1
create table dept(
deptno int auto_increment primary key,
dname varchar(15),
loc varchar(50)
)engine=innodb;

insert into dept values(1,'it','gz');
insert into dept values(2,'cw','sh');
insert into dept values(3,'hr','sz');
commit;

select * from dept;
desc dept;

問題1：
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'(2)
解決問題：
上面的報錯，說明有安裝MySQL,需要卸載掉現有的安裝包
rpm -qa|grep mysql
yum remove mysql
只需刪除掉mysql,mysql-devel包。

問題2：
mysqld: [ERROR] Could not open required defaults file: /mysql/data/3306/my.cnf
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

解決思路：
提示沒找到my.cnf文件，請檢查初始化命令的路徑與my.cnf存放的路徑是否一致。

問題3：[root@itpuxdb 3306]# /etc/init.d/mysql start
Starting MySQL..The server quit without updating PID file ([FAILED]ata/3306/mysql.pid).

這樣的問題會導致不能生成pid,sock文件。

解決方法：
把/etc/my.cnf 文件，重命名為my.cnf.bak ，這樣就能在/mysql/data/3306/目錄下可以生成相應的文件了。