生产环境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/目录下可以生成相应的文件了。