MySQL 安装与使用

MySQL 安装与使用

1.二进制安装包安装

[root@hadoop001 ~]#  cd /usr/local
[root@hadoop001 local]# rz //上传mysql安装包
[root@hadoop001 local]# tar -xzvf mysql-5.6.23-linux-glibc2.5-x86_64 //解压
[root@hadoop001 local]# ln -s mysql-5.6.23-linux-glibc2.5-x86_64/ mysql //软连接
[root@hadoop001 local]# ll
total 0
drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxr-xr-x.  2 root root   6 Apr 11  2018 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
lrwxrwxrwx.  1 root root  35 Nov 27 14:21 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64/
drwxr-xr-x. 13 root root 213 Nov 27 14:13 mysql-5.6.23-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
drwxr-xr-x.  5 root root  49 Nov  3 20:53 share
drwxr-xr-x.  2 root root   6 Apr 11  2018 src
[root@hadoop001 local]# groupadd -g 101 dba //添加dba用户组
[root@hadoop001 local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin //添加用户
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
[root@hadoop001 local]# su - mysqladmin //缺少样式
-bash-4.2$ 
-bash-4.2$ exit
logout
[root@hadoop001 local]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)
[root@hadoop001 local]# cp /etc/skel/.* /usr/local/mysql //复制隐藏文件
cp: omitting directory ‘/etc/skel/.’
cp: omitting directory ‘/etc/skel/..’
[root@hadoop001 local]# su - mysqladmin 
Last login: Wed Nov 27 14:40:15 CST 2019 on pts/0
[mysqladmin@hadoop001 ~]$ 
[mysqladmin@hadoop001 ~]$ exit
logout
[root@hadoop001 local]# cp /etc/my.cnf /etc/my.cnf000 //务必复制一份原配置文件
[root@hadoop001 local]# vi /etc/my.cnf //修改配置文件
[client]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
 
[mysqld]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock

skip-external-locking
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
query_cache_size= 32M
max_allowed_packet = 16M
myisam_sort_buffer_size=128M
tmp_table_size=32M

table_open_cache = 512
thread_cache_size = 8
wait_timeout = 86400
interactive_timeout = 86400
max_connections = 600

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 32

#isolation level and default engine 
default-storage-engine = INNODB
transaction-isolation = READ-COMMITTED

server-id  = 1
basedir     = /usr/local/mysql
datadir     = /usr/local/mysql/data
pid-file     = /usr/local/mysql/data/hostname.pid

#open performance schema
log-warnings
sysdate-is-now

binlog_format = MIXED
log_bin_trust_function_creators=1
log-error  = /usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
#other logs
#general_log =1
#general_log_file  = /usr/local/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/usr/local/mysql/data/slow_log.err

#for replication slave
#log-slave-updates 
#sync_binlog = 1

#for innodb options 
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:500M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/arch
innodb_log_files_in_group = 2
innodb_log_file_size = 200M


innodb_buffer_pool_size = 2048M //生产6、8G
innodb_additional_mem_pool_size = 50M
innodb_log_buffer_size = 16M

innodb_lock_wait_timeout = 100
#innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
innodb_locks_unsafe_for_binlog=1

#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on

#case-sensitive file names and separate tablespace
innodb_file_per_table = 1
lower_case_table_names=1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[root@hadoop001 local]# chown mysqladmin:dba /etc/my.cnf //修改配置文件所属用户及用户组
[root@hadoop001 local]# chmod 640 /etc/my.cnf //修改权限
[root@hadoop001 local]# ll /etc/my.cnf //查看配置是否正确
-rw-r-----. 1 mysqladmin dba 2232 Nov 27 14:53 /etc/my.cnf
[root@hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql
//必须做  [root@hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql/*  
[root@hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64
[root@hadoop001 local]# chmod -R 755 /usr/local/mysql
//必须做 [root@hadoop001 local]# chmod -R 755 /usr/local/mysql/*  
[root@hadoop001 local]# chmod -R 755 /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64
[root@hadoop001 local]# ll
total 0
drwxr-xr-x.  2 root       root   6 Apr 11  2018 bin
drwxr-xr-x.  2 root       root   6 Apr 11  2018 etc
drwxr-xr-x.  2 root       root   6 Apr 11  2018 games
drwxr-xr-x.  2 root       root   6 Apr 11  2018 include
drwxr-xr-x.  2 root       root   6 Apr 11  2018 lib
drwxr-xr-x.  2 root       root   6 Apr 11  2018 lib64
drwxr-xr-x.  2 root       root   6 Apr 11  2018 libexec
lrwxrwxrwx.  1 mysqladmin dba   35 Nov 27 14:21 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64/
drwxr-xr-x. 13 mysqladmin dba  269 Nov 27 14:42 mysql-5.6.23-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root       root   6 Apr 11  2018 sbin
drwxr-xr-x.  5 root       root  49 Nov  3 20:53 share
drwxr-xr-x.  2 root       root   6 Apr 11  2018 src
[root@hadoop001 ~]# yum install -y perl
[root@hadoop001 ~]# yum install -y autoconf
[root@hadoop001 ~]# yum install -y libaio
[root@hadoop001 ~]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ pwd
/usr/local/mysql
[mysqladmin@hadoop001 ~]# mkdir arch
[mysqladmin@hadoop001 ~]$ scripts/mysql_install_db  --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 
Installing MySQL system tables...2019-11-27 15:29:36 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2019-11-27 15:29:36 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

Filling help tables...2019-11-27 15:29:44 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2019-11-27 15:29:44 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
  /usr/local/mysql/bin/mysqladmin -u root -h hadoop001 password 'new-password'

Alternatively you can run:

  /usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/local/mysql/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql/my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@hadoop001 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql
[root@hadoop001 mysql]# chmod +x /etc/rc.d/init.d/mysql //增加执行权限
[root@hadoop001 mysql]# chkconfig --del mysql
[root@hadoop001 mysql]# chkconfig --add mysql
[root@hadoop001 mysql]# chkconfig --level 345 mysql on //mysql自启动
[root@hadoop001 mysql]# vi /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

su - mysqladmin -c "/etc/init.d/mysql start --federated"
[root@hadoop001 ~]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ rm -f my-new.cnf my.cnf 
[mysqladmin@hadoop001 ~]$ service mysql start
Starting MySQL.. SUCCESS! 
[mysqladmin@hadoop001 ~]$ service mysql status
 SUCCESS! MySQL running (11578)
[mysqladmin@hadoop001 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Database changed
mysql> update user set password=password('123456') where user='root'; //增加密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| hadoop001 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost |      |                                           |
| hadoop001 |      |                                           |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> delete from user where user=''; //删除无用用户
Query OK, 2 rows affected (0.00 sec)

mysql> select host,user,password from user; 
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| hadoop001 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[mysqladmin@hadoop001 ~]$ vi .bashrc 
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1  //目录文件显示全路径
[mysqladmin@hadoop001 ~]$ source .bashrc 
[mysqladmin@hadoop001 ~]$ echo $MYSQL_HOME
/usr/local/mysql
[mysqladmin@hadoop001 ~]$ which mysql
~/bin/mysql
[mysqladmin@hadoop001 ~]$ 

重新部署方法：
rm -rf arch/* data/*
binlog文件 数据文件

scripts/mysql_install_db
–user=mysqladmin
–basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data
重新下载

2.部署出问题

① 检查执行的目录 用户
② 重新部署
rm -rf arch/* data/* //binlog文件 数据文件
从新下载
scripts/mysql_install_db
–user=mysqladmin
–basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data

3.window 部署 dbeaver 海狸

一、安装dbeaver首先需要部署java环境。
windows配置java1.8。

二、下载并使用dbeaver。
DBeaver官网 https://dbeaver.jkiss.org/download/

解压下载的压缩包，运行文件夹中的dbeaver.exe文件

运行dbeaver

输入配置信息

mysql> select user,password,host from user;
+------+-------------------------------------------+-----------+
| user | password                                  | host      |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | hadoop001 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 127.0.0.1 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | ::1       |
+------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> grant all privileges on *.* to root@'%' identified by '123456';//增加用户
Query OK, 0 rows affected (0.00 sec)

mysql> select user,password,host from user;
+------+-------------------------------------------+-----------+
| user | password                                  | host      |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | hadoop001 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 127.0.0.1 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | ::1       |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
+------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

连接成功