linux系统安装mysql数据库

Linux下安装mysql数据库
系统环境：linux redHat7
数据库版本：Mysql5.7
连接服务器软件：xshell
xftp

上传二进制安装包及配置文件

安装包及配置文件复制到/tmp目录下

解压二进制安装包

 tar -xvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz

修改安装包路径

mv -rf mysql-5.7.12-linux-glibc2.5-x86_64 /usr/local/mysql

创建用户

useradd mysql

创建数据文件路径

mkdir -p /home/mysql3306/mysql3306
mkdir -p /home/mysql3306/log

数据库文件路径: /home/mysql3306/mysql3306
日志文件路径: /home/mysql3306/log

修改相关文件权限

chown mysql:mysql -R /home/mysql3306
chown mysql:mysql -R /usr/local/mysql

更改配置文件

[root@cent1 tmp]# cp my3306.cnf /etc/my3306.cnf
vim /etc/my3306.cnf
[mysql]
# basic settings #
user = mysql
character_set_server=utf8mb4
collation_server=utf8mb4_bin
default-storage-engine=innodb
pid-file=/home/mysql3306/mysql.pid
socket=/tmp/mysql.sock
basedir= /usr/local/mysql
datadir= /home/mysql3306/mysql3306
tmpdir=/tmp
transaction-isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
lower_case_table_names=1
max_allowed_packet = 32M
slave_pending_jobs_size_max = 32M
event_scheduler = 1
server-id=1383306
port=3306
symbolic-links=0
sysdate-is-now #MySQL会把sysdate()当成now()的一个同义词

# connection #
interactive_timeout = 200
wait_timeout = 200
lock_wait_timeout = 1800
skip_name_resolve = 1
max_connections = 2000
max_connect_errors = 100000

# table cache performance settings #（待定）
table_open_cache = 3000
table_definition_cache = 4096
table_open_cache_instances = 16

# session memory settings #（待定）
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 4M
tmp_table_size = 100M
max_heap_table_size = 100M
join_buffer_size = 4M
thread_cache_size = 64  #观察 Connections and Threads_created 两者的变化速率
# log settings #
general_log_file=/home/mysql3306/log/general.log
log-error=/home/mysql3306/log/error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file=/home/mysql3306/log/slow-query.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog-rows-query-log-events = 1
log-bin=/home/mysql3306/mysql-bin
log-bin-index=/home/mysql3306/mysql-bin.index
log_bin_trust_function_creators = 1
binlog_cache_size=4M
max_binlog_cache_size = 4096M
max_binlog_size = 1024M
expire-logs-days=3
log-slave-updates = 1
relay-log-index=/home/mysql3306/mysql-relay-bin.index
relay-log=/home/mysql3306/mysql-relay-bin
log_timestamps=system
#log_warnings =2 #此参数已过期，改用下面的log_error_verbosity=2
log_error_verbosity=2

# innodb settings #
innodb_page_size = 16384
innodb_data_home_dir=/home/mysql3306
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_buffer_pool_size = 1G  #（待定）
innodb_buffer_pool_instances = 1
innodb_buffer_pool_dump_pct = 40
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout=1
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 200
innodb_max_dirty_pages_pct =75
innodb_io_capacity_max = 1000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_undo_directory=/home/mysql3306  #新增，便于管理
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1 #用于打开/关闭undo log 在线truncate特性
innodb_max_undo_log_size = 2G
innodb_purge_threads = 4
innodb_purge_rseg_truncate_frequency = 128 #想要增加释放回滚区间的频率，就得降低该值
innodb_flush_neighbors = 0 #ssd0，非ssd1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_sync_spin_loops=0 #自旋锁的轮转数，关闭可节省CPU资源
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_write_io_threads = 16  #（待定）
innodb_read_io_threads = 16   #（待定）
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size=1G

# about query_cache #
query_cache_type=0
# replication settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
sync-relay-log =0 #设为1更安全但会显著增加主从延迟
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = ROW
binlog_rows_query_log_events = 1
relay_log_recovery = 1
slave_parallel_workers=20
slave-parallel-type = LOGICAL_CLOCK
slave_transaction_retries=128
#slave_skip_errors = ddl_exist_errors
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
log-slave-updates=true
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
slave_preserve_commit_order=1 #对于多线程slaves，来保障事务在slave上执行的顺序与relay log中的顺序严格>一致，只有当“slave_parallel_workers”开启时有效
read_only=0

# GTID settings #
gtid-mode=on
enforce-gtid-consistency=true
binlog_gtid_simple_recovery=1 #这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和
binlog日志清理更快。

# SEMI Replication setting
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled =1
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=5000

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
user=mysql
open-files-limit=65536
log-error=/home/mysql3306/log/error.log

pid 文件路径
pid-file=/home/mysql3306/mysql.pid
sock生成文件路径(登陆时用)
socket=/tmp/mysql.sock
数据库文件路径
basedir= /usr/local/mysql
数据文件路径
datadir= /home/mysql3306/mysql3306
日志文件相关
general_log_file=/home/mysql3306/log/general.log
log-error=/home/mysql3306/log/error.log
slow_query_log_file=/home/mysql3306/log/slow-query.log
注意涉及路径的参数需要改成本地自定义的路径如果路径更改的不全，在初时话数据库时会产生报错，可以在报错日志的路径里查看相关报错日志

初始化数据库

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize-insecure --basedir=/usr/local/mysql --
datadir=/home/mysql3306/mysql3306/ --user=mysql

启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3306.cnf --datadir=/home/mysql3306/mysql3306/ --user=mysql &

进入数据库

/usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock

初始化用户权限

Alter user root@'localhost' identified by 'root'

多实例安装

重复创建文件夹的部分，创建一个mysql3307的文件夹，复制一份新的配置文件
更改配置文件中相应路径
用新的配置文件初始化数据库
初始化成功后用新的配置文件启动数据库
使用对应的sock文件登陆对应的数据库