生产环境mysql安装_生产环境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

测试

[[email protected] ~]# mysql --version

卸载系统自带的mysql

yum remove mysql

[[email protected] ~]# 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="\[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

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的检查顺序

[[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/目录下。

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 [email protected]%‘ identified by ‘rootroot‘ with grant option;

flush privileges;

创建数据库

create database db1

创建用户

create user dbuser identified by "dbuser";

mysql> grant all privileges on dbuser.to [email protected] identified by ‘dbuser‘;

mysql> grant all privileges on.to [email protected]%‘ identified by ‘dbuser‘ with grant option;

mysql> grant all privileges on dbuser. to [email protected]%‘ 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：[[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/目录下可以生成相应的文件了。