Mysql安装

[mysqldump]
socket = /home/mysql/data/mysqld.sock
[mysqladmin]
socket = /home/mysql/data/mysqld.sock
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
socket = /home/mysql/data/mysqld.sock
[client]
port = 3306
#default-character-set = utf8
socket = /home/mysql/data/mysqld.sock
[mysqld]
symbolic-links=0
basedir = /usr/local/mysql
datadir = /home/mysql/data
tmpdir = /home/mysql/tmp/
log-error = /home/mysql/data/mysql-error.log
slow_query_log = on
slow_query_log_file = /home/mysql/data/mysql-slow.log
long_query_time = 5
log_queries_not_using_indexes = on
pid-file = /home/mysql/data/mysqld.pid
log-bin = /home/mysql/binlog/mysql-bin
server_id = 1
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
socket = /home/mysql/data/mysqld.sock

gtid_mode=ON
enforce_gtid_consistency=on
user = mysql
port            = 3306
server-id       = 1
core-file
binlog_format = ROW
log_output = FILE
character_set_server = utf8
auto_increment_increment = 1
auto_increment_offset = 1
query_cache_type = 0
long_query_time = 3
max_connections = 1024
max_connect_errors = 10240
#local_infile = 0
general_log = OFF
slow_query_log = ON
relay-log = mysqld-relay-bin
expire_logs_days = 3
innodb_io_capacity = 500 # SSD 2000 ~ 20000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_lock_wait_timeout = 100
innodb_buffer_pool_size = 1G
innodb_print_all_deadlocks = ON
#innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_autoextend_increment = 64
innodb_thread_concurrency = 0
#innodb_old_blocks_time = 1000
innodb_buffer_pool_instances = 8
thread_cache_size = 200
innodb_lru_scan_depth = 512
innodb_flush_neighbors = 1

innodb_checksum_algorithm = crc32
table_definition_cache = 8192
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup = on
innodb_read_io_threads = 4
innodb_adaptive_flushing = ON
innodb_log_buffer_size = 32M
innodb_purge_threads = 2
performance_schema = ON
innodb_write_io_threads = 4
#skip-name-resolve = ON
#skip_external_locking = ON
max_allowed_packet = 64M
table_open_cache = 8192
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
lower_case_table_names = 1
log_bin_trust_function_creators = 1
slave_net_timeout = 30
innodb_adaptive_hash_index=off
innodb_adaptive_flushing_lwm=50
innodb_log_write_ahead_size=16384
innodb_flush_log_at_timeout=1
log_timestamps=system
innodb_random_read_ahead=ON
innodb_change_buffering=none
#validate_password_policy=LOW
#validate_password_mixed_case_count = 0
#validate_password_number_count = 0

高版本有一个报错，删除一个属性配置即可。

然后注意配置文件定义好的目录，去创建对应目录，不然后面会报错

mkdir /home/mysql/data

mkdir /usr/local/mysql

mkdir /home/mysql/tmp/

mkdir /home/mysql/binlog

文件夹需要授权

chown -R mysql:mysql /home/mysql

注意配置文件的两个地址，下面初始化会用到：

basedir = /usr/local/mysql

datadir = /home/mysql/data

初始化MySQL

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

注意查看日志文件

cd /home/mysql/data

cat mysql-error.log

如果没有报错，这里面的内容大概是这样的

2020-01-03T01:20:10.072442-08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 100 200 300 400 500 600 700 800 900 1000 100 200 300 400 500 600 700 800 900 1000 100 200 300 400 500 600 700 800 900 10002020-01-03T01:20:12.381557-08:00 0 [Warning] InnoDB: New log files created, LSN=457912020-01-03T01:20:12.426037-08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.2020-01-03T01:20:12.495351-08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3eb70318-2e0a-11ea-a5e4-000c29050520.2020-01-03T01:20:12.497215-08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2020-01-03T01:20:12.500067-08:00 1 [Note] A temporary password is generated for root@localhost: 9Myi??wpSpXO

#a9QPa?A4?lr

最后的 9Myi??wpSpXO 是root用户的初始化密码，在第一次登录MySQL时使用

如果出现报错，也去查看日志文件报错情况，按具体问题处理，报错处理完以后一般需要清空data文件夹下生成的所有文件，然后再重新进行初始化，否则初始化会报错：

2020-01-03T01:08:22.596631-08:00 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

把/home/mysql/data文件夹属性改为mysql用户

cd /home/mysql

chown -R mysql data

6.启动MySQL

[root@localhost mysql]#/usr/local/mysql/bin/mysqld_safe --user=mysql &

[1] 4847

查看进程

[root@localhost mysql]# ps aux | grep mysqld

root 4847 0.1 0.0 106192 1572 pts/3 S 01:27 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql

mysql 5897 0.8 14.4 2082172 275228 pts/3 Sl 01:27 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/home/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/home/mysql/data/mysql-error.log --pid-file=/home/mysql/data/mysqld.pid --socket=/home/mysql/data/mysqld.sock --port=3306

root 5927 0.0 0.0 103304 900 pts/3 S+ 01:27 0:00 grep mysqld

7.通过初始密码登录MySQL并修改密码

[root@localhost mysql]# /usr/local/mysql/bin/mysqladmin -uroot -p password

Enter password: 初始密码（日志文件里面的内容 9Myi??wpSpXO ）

New password: 新密码

Confirm new password:再次输入密码

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

8.关闭MySQL服务，查看是否成功

[root@localhost mysql]# /usr/local/mysql/bin/mysqladmin -uroot -p shutdown

Enter password:root

2020-01-03T09:31:01.197162Z mysqld_safe mysqld from pid file /home/mysql/data/mysqld.pid ended[1]+ Done /usr/local/mysql/bin/mysqld_safe --user=mysql

9.设置开机自启

复制文件

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

增加mysql 服务控制脚本执行权限

[root@localhost mysql]# chmod +x /etc/init.d/mysql

将mysql服务加入到系统服务

[root@localhost mysql]# chkconfig --add mysql

检查mysql是否生效

[root@localhost mysql]# chkconfig --list mysql

mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off

显示已经生效

以后就可以使用service命令控制mysql的启动和停止了

[root@localhost mysql]# service mysql start

Starting MySQL. SUCCESS!

10.配置全局环境变量

[root@localhost mysql]# vi /etc/profile

在最后增加：

PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH

export PATH

使设置的环境变量生效：

[root@localhost mysql]# source /etc/profile

11.登录数据库

su - mysql

mysql -uroot -proot

完成

四、常见命令

一、mysql服务器执行sql

1，将要导入的.sql文件移至/home/mysql/sql文件下，这样的路径比较方便

2，进入MySQL：mysql -u用户名 -p密码

如我输入的命令行:mysql -uroot -proot (输入同样后会让你输入mysql的密码)

3，新建一个名为blog的目标数据库 mysql>create database blog;

4.输入：mysql>use 目标数据库名

如我输入的命令行:mysql>use blog;

5，导入文件：mysql>source 导入的文件名;

如我输入的命令行：mysql>source /home/mysql/sql/blog.sql;

二、mysql ip访问权限

1. 授权用户root使用密码root从任意主机连接到mysql服务器：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

flush privileges;

2.授权用户root使用密码jb51从指定ip为218.12.50.60的主机连接到mysql服务器：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'218.12.50.60' IDENTIFIED BY 'root' WITH GRANT OPTION;

flush privileges;

三、新建数据库

CREATE DATABASE nacos_config DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

四、重启mysql

service mysql start (5.5.7版本命令)

service mysqld stop

/etc/inint.d/mysqld start

/etc/inint.d/mysqld stop

service mysqld restart

service mysql restart (5.5.7版本命令)

五、用户

查询用户密码 mysql> select host,user,authentication_string from mysql.user;

设置（或修改）root用户密码 mysql> use mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

mysql> flush privileges;

退出：mysql> quit