初始化mysql,连接数据库,并配置开机自启动
配置参数
cd /opt/soft/mysql
vim ./my.cnf
点击i,进入编辑模式
[mysql] # 默认字符集 default-character-set=utf8mb4 [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 server-id = 3306 user = mysql socket = /tmp/mysql.sock # 安装目录 basedir = /opt/soft/mysql # 数据存放目录 datadir = /opt/soft/mysql/datas/mysql log-bin = /opt/soft/mysql/datas/mysql/mysql-bin innodb_data_home_dir =/opt/soft/mysql/datas/mysql innodb_log_group_home_dir =/opt/soft/mysql/datas/mysql #日志及进程数据的存放目录 log-error =/opt/soft/mysql/datas/mysql/mysql.log pid-file =/opt/soft/mysql/datas/mysql/mysql.pid # 服务端使用的字符集默认为8比特编码 character-set-server=utf8mb4 lower_case_table_names=1 autocommit =1 ##################以上要修改的######################## skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 1024 sort_buffer_size = 4M net_buffer_length = 8K read_buffer_size = 4M read_rnd_buffer_size = 512K myisam_sort_buffer_size = 64M thread_cache_size = 128 #query_cache_size = 128M tmp_table_size = 128M explicit_defaults_for_timestamp = true max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 binlog_format=mixed binlog_expire_logs_seconds =864000 # 创建新表时将使用的默认存储引擎 default_storage_engine = InnoDB innodb_data_file_path = ibdata1:10M:autoextend innodb_buffer_pool_size = 1024M innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 transaction-isolation=READ-COMMITTED [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 256M sort_buffer_size = 4M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
注意(在查看模式下G:直接到最后一行
10G:直接转到第十行)
esc :wq
数据库初始化
输入:mysqld --defaults-file=/opt/soft/mysql/my.cnf --basedir=/opt/soft/mysql/ --datadir=/opt/soft/mysql/datas/mysql --user=mysql --initialize-insecure
参数(重要)
-
defaults-file
:指定配置文件(要放在–initialize 前面) -
user
: 指定用户 -
basedir
:指定安装目录 -
datadir
:指定初始化数据目录 -
intialize-insecure
:初始化无密码
如果出现bash: mysqld: command not found
source /etc/profile (跟新配置文件)
再次输入mysqld --defaults-file=/opt/soft/mysql/my.cnf --basedir=/opt/soft/mysql/ --datadir=/opt/soft/mysql/datas/mysql --user=mysql --initialize-insecure
ll ./datas/mysql/
启动MySQL
mysqld_safe --defaults-file=/opt/soft/mysql/my.cnf &
出现代码后,回车再输入
ps -ef|grep mysql(查看是否启动)
mysql -uroot --skip-password(无密码登录)
如果没进入
mysql -uroot -p
输入密码登录
修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
刷新权限
flush privileges;
exit
mysql -uroot -
设置允许远程登录
mysql> show databases;查看所有的数据库名
mysql> use mysql; 选中指定的数据库
mysql> show tables; 显示mysql数据库中所有的表
mysql> select * from user; 查询表数据
设置root用户远程也可以访问
mysql> update user set user.Host='%' where user.User='root';(%表示任何网址都可以访问,开启root'用户远程访问)
mysql> flush privileges;(更新设置)
至此数据库初始化已完成
连接数据库
安装DataGrip并打开,
新建 MySql,进行设置
Name:db149
Host:192.168.153.149
User:root
Password:root
点击Test Connection
完成连接
至此可以直接输入相关命令进行数据库的操作
设置开机自启动
配置mysql8开机自启:
1.进入:cd /etc/rc.d/init.d 如果有mysqld,删除该文件: [root@localhost init.d]# rm -rf ./mysqld
2.创建sh文件: [root@localhost init.d]# vim ./autostartmysql.sh
3.配置:
#!/bin/sh chkconfig: 2345 10 90 description: myservice .... /opt/soft/mysql/bin/mysqld_safe --defaults-file=/opt/soft/mysql/my.cnf &
4.设置权限: [root@localhost init.d]# chmod +x ./autostartmysql.sh [root@localhost init.d]# chkconfig --add autostartmysql.sh [root@localhost init.d]# chkconfig autostartmysql.sh on [root@localhost init.d]# chkconfig --list
5.重启: [root@localhost init.d]# shutdown -r now
完成后建议重新启动虚拟机
注:如果grip中Test出现[08001] Could not create connection to database server. Attempted reconnect问题
是没有给root重新设置密码的原因
须进入Xshell7 中Test
输入 mysql
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
mysql>flush privileges;
mysql>use mysql;
mysql>update user set user.Host='%' where user.User='root';
mysql>flush privileges;
mysql>exit
输入mysql -uroot -p(再重新登录mysql)
输入密码root
再转到grip上新建数据库连接,即可成功
在输入show databases;执行,运行完成表示成功。