生产环境Linux二进制安装配置MySQL

生产环境linux二进制安装配置MysqL

一、环境检查
rpm -qa |grep libaio #如果没有安装libaio则需要安装。
df -h@H_322_5@mount /dev/sr0 /mnt
二、安装过程
1）上传安装程序
通过FTP/SFTP上传安装包
2）创建创用户,组，创建目录
groupadd MysqL
useradd -r -g MysqL -s /bin/false MysqL@H_322_5@mkdir -p /MysqL/data/3306/data
这里的3306是MysqL默认端口号，/MysqL/data目录MysqL的安装目录,
/MysqL/data/3306/data是MysqL数据目录。@H_322_5@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

测试
[[email protected] ~]# MysqL --version
卸载系统自带的MysqL
yum remove MysqL
[[email protected] ~]# which MysqL
/MysqL/app/MysqL/bin/MysqL
6）准备参数文件@H_322_5@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="\[email protected] \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@H_322_5@max_connections = 800@H_322_5@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@H_322_5@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@H_322_5@myisam_max_sort_file_size=10G@H_322_5@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@H_322_5@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@H_322_5@mysqLd --defaults-file=/MysqL/data/3306/my.cnf --initialize --datadir=/MysqL/data/3306/data --basedir=/MysqL/app/MysqL --user=MysqL

cd /MysqL/log/3306@H_322_5@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

第二处要改：@H_322_5@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 &

@H_300_4@my.cnf的检查顺序
[[email protected] 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/目录下。

@H_300_4@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）后期配置（改密码，启动远程访问）
登录@H_322_5@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
@R_434_10589@
create user dbuser identified by "dbuser";@H_322_5@mysqL> grant all privileges on dbuser. to ‘dbuser‘@‘localhost‘ identified by ‘dbuser‘;@H_322_5@mysqL> grant all privileges on . to ‘dbuser‘@‘%‘ identified by ‘dbuser‘ with grant option; @H_322_5@mysqL> grant all privileges on dbuser. to ‘dbuser‘@‘%‘ identified by ‘dbuser‘;@H_322_5@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：@H_322_5@mysqLd: [ERROR] Could not open required defaults file: /MysqL/data/3306/my.cnf@H_322_5@mysqLd: [ERROR] Fatal error in defaults handling. Program aborted!

解决思路：
提示没找到my.cnf文件，请检查初始化命令的路径与my.cnf存放的路径是否一致。

问题3：[[email protected] 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/目录下可以生成相应的文件了。