ps: ~ 迁移内容在最下面~

目录：
	一、安装
	二、安装过程中可能遇到的问题
	三、数据迁移

一、安装

1.获取mysql

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz
(安装wget命令：yum install wget -y)

2.解压mysql

xz -d mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz
 tar -xvf mysql-8.0.15-linux-glibc2.12-x86_64.tar

3.移动文件到安装目录/usr/local/mysql

mv mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysql

4.添加mysql用户

adduser mysql

5.添加编辑/etc/my.cnf配置文件

[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
# Skip #
skip_name_resolve = 1
skip_external_locking = 1 
skip_symbolic_links = 1
# GENERAL #
user = mysql
default_storage_engine = InnoDB
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
pid_file = /usr/local/mysql/data/mysqld.pid
basedir = /usr/local/mysql
port = 3306
bind-address = 0.0.0.0
explicit_defaults_for_timestamp = off
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#read_only=on
# MyISAM #
key_buffer_size = 32M
#myisam_recover = FORCE,BACKUP
# undo log #
innodb_undo_directory = /usr/local/mysql/undo
innodb_undo_tablespaces = 8
# SAFETY #
max_allowed_packet = 100M
max_connect_errors = 1000000
sysdate_is_now = 1
#innodb = FORCE
#innodb_strict_mode = 1
secure-file-priv='/tmp'
default_authentication_plugin='mysql_native_password'
# Replice #
server-id = 1001
relay_log = mysqld-relay-bin
gtid_mode = on
enforce-gtid-consistency
log-slave-updates = on
master_info_repository =TABLE
relay_log_info_repository =TABLE
# DATA STORAGE #
datadir = /usr/local/mysql/data/
tmpdir = /tmp
# BINARY LOGGING #
log_bin = /usr/local/mysql/sql_log/mysql-bin
max_binlog_size = 1000M
binlog_format = row
binlog_expire_logs_seconds=86400
# sync_binlog = 1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 4000
thread_cache_size = 2048
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
# thread_concurrency = 24
join_buffer_size = 1M
# table_cache = 32768
thread_stack = 512k
max_length_for_sort_data = 16k
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 256M
#innodb_buffer_pool_instances = 8
innodb_stats_on_metadata = off
innodb_open_files = 8192
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 60
innodb_old_blocks_time=1000
innodb_use_native_aio = 1
innodb_purge_threads=1
innodb_change_buffering=all
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_data_file_path = ibdata1:256M:autoextend
innodb_rollback_on_timeout=on
# LOGGING #
log_error = /usr/local/mysql/sql_log/mysql-error.log
# log_queries_not_using_indexes = 1
# slow_query_log = 1
slow_query_log_file = /usr/local/mysql/sql_log/slowlog.log
# TimeOut #
#interactive_timeout = 30
#wait_timeout = 30
#net_read_timeout = 60
[mysqldump]
quick
max_allowed_packet = 100M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

6.生成data sql_log undo目录，并修改属主

mkdir data sql_log undo
chown mysql:mysql -R data/ sql_log/ undo/

7.将/user/local/mysql/bin路径添加到/etc/profile中

export PATH=$PATH:/usr/local/mysql/bin
执行命令 source /etc/profile重启

8.初始化mysql.

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

9.拷贝support-files目录下的mysql.server到/etc/init.d/mysqld

cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysqld

10.启动mysql服务

   /etc/init.d/mysqld start
   查看mysql进程：ps -ef | grep mysql
   设置开机启动：
   赋予可执行权限：chmod +x /etc/init.d/mysqld
   添加服务：chkconfig --add mysqld
   显示服务列表：chkconfig --list
   如果看到mysql的服务，并且3,4,5都是开的话则成功，如果是关，则键入：chkconfig --level 345 mysqld on

11.查询初试密码sql_log/mysql-error.log

cd /usr/local/mysql/sql_log
grep password mysql-error.log

12.使用上面的初试密码登录mysql

mysql -uroot -p

13.修改密码，这里密码设置为123456

ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
FLUSH PRIVILEGES;

14.设置可以外部连接（Navicat等）

进入mysql：
	mysql -u root -p
进入mysql数据库：
	use mysql;
查看user表中的数据：
	select Host, User from user;
修改user表中的Host:
	update user set Host='%' where User='root';
最后刷新一下：
	flush privileges;

15.至此安装完成！

如果安装过程中遇到问题，看下面有没有相同问题的解决办法

二、安装过程中可能遇到的问题

1.执行mysqld报错 1

解决办法：修改这几个文件权限 data sql undo

2.执行mysqld报错 2

解决办法：
	sudo mkdir -p /usr/local/mysql/sql_log
	sudo chown -R mysql:mysql /usr/local/mysql/sql_log

3.启动报错

解决办法：
	sudo rm -rf /usr/local/mysql/data/*
	sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

4.第9步/usr/etc下面为空，找不到mysql.server

解决办法：
	/usr/local/mysql/support-files，目录下可以找到

5.用mysql5的修改密码命令修改了密码后，登不了了

update mysql.user set authentication_string=password(‘123456’) where user=‘root’;

解决办法：
	i. 试了各种方法无效，最终有效的办法：删除当前的 root 用户并重新创建：
		DROP USER 'root'@'%';
		CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
		GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
		FLUSH PRIVILEGES;
	ii. 使用mysql 8的命令从新改密码
		ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
		FLUSH PRIVILEGES;

三、数据迁移

1. 在旧服务器上备份sql

mysqldump -uroot -p --databases db1 db2 > backup.sql

2. 在旧服务器上传输备份数据到新服务器

scp backup.sql root@10.0.x.x:/data/web_root/dbs

3.新服务器上恢复sql数据

mysql -uroot -p < backup.sql