mysql 5.6 二进制安装_mysql二进制安装

mysql赋予权限,授权后用户就有了密码.登录就需密码:

grant all on *.* to root@'localhost' identified by 'root' with

grant option;

grant all

on *.* to root@'%' identified by 'root' with grant

option;

flush

privileges;---刷新系统权限相关表。

说明：*.* 指所有库的所有对象，

*数据库*表

如: test1.*

test1.t1

root@'localhost'

指root用户名在localhost网段。root@'%'指代所有网段，例如：root@'192.168.2.%'

identified by

'root'; 密码是root 。with grant

option 给该用户复权权限。

本地登录数据库：

[root@lbg ~]# /usr/local/mysql/bin/mysql

-uroot -proot -S /tmp/mysql3306.sock

--默认端口号

[root@lbg ~]# /usr/local/mysql/bin/mysql -uroot

-proot

---读参数文件中的socket文件

[root@lbg ~]# /usr/local/mysql/bin/mysql -uroot

-p

---不明文写密码

[root@lbg ~]# mysql -p

---默认root用户登录

使用远程连接数据库：

[root@lbg ~]# /usr/local/mysql/bin/mysql

-uroot -proot -h192.168.88.8 -P3306

9.关闭数据库

正常关闭方式：

[root@lbg

~]#  /usr/local/mysql/bin/mysqladmin

-uroot -proot -S /tmp/mysql3306.sock

shutdown

说明：mysqladmin还可用于修改密码。

⽆密码修改： mysqladmin -u root password

123456

--设置密码为123456

已有密码修改：mysqladmin -u root -p password 123456

---会提示输入旧密码。

直接kill关闭数据库：

[root@lbg ~]# ps -ef |grep mysql

root

2543  2127  0 15:43

pts/1    00:00:00 /bin/sh

/usr/local/mysql/bin/mysqld_safe --user=mysql

mysql

3402  2543 22 15:43

pts/1    00:00:01

/usr/local/mysql/bin/mysqld -basedir=/usr/local/mysql

--datadir=/home/mysql3306/mysql3306

--plugin-dir=/usr/local/mysql/lib/plugin --user=mysql

--log-error=/home/mysql3306/logs/mysql-error.log

--open-files-limit=65535 --pidfile=/home/mysql3306/mysql.pid

--socket=/tmp/mysql3306.sock --port=3306

[root@lbg ~]# kill

3402  2543

---子进程和父进程都关闭

10.删除mysql

pkill mysql

rm -rf

/usr/local/mysql

rm -rf

/home/mysql3306/mysql3306/*

rm -rf

/home/mysql3306/logs/*

mysql5.7安装：

mysql5.7安装步骤中与mysql5.6的不同在于初始化，其他大致相同。

[root@lbg soft]# mv mysql-5.7.23-linux-glibc2.12-x86_64

/usr/local/mysql

1.mysql5.7初始化：

[root@lbg soft]#

/usr/local/mysql/bin/mysqld

--defaults-file=/etc/my.cnf --initialize-insecure

--basedir=/usr/local/mysql --user=mysql

说明：

初始化是使用 /usr/local/mysql/bin/mysqld。

--defaults-file=/etc/my.cnf

一定写到第一个参数

--initialize-insecure 初始化(不会设置初始化密码 5.6一样,密码为空)

--initialize 初始化(会设置初始化密码,生成随机密码,在eror日志可以找到)

2.查看初始化后的文件

[root@lbg mysql3306]# ls

auto.cnf

ibdata1

ib_logfile1

mysql-bin.000001

performance_schema  undo001

undo003

ib_buffer_pool  ib_logfile0

mysql

mysql-bin.index

sys

undo002

3.mysql5.7启动登录

[root@lbg soft]# mysqld_safe --user=mysql

&

---启动

[root@lbg soft]# mysql

---登录

4.授权、查看系统版本

mysql> select user,host,authentication_string from

mysql.user; --不再是password了。

mysql>  delete from mysql.user

where user like '%mysql%';

mysql>  grant all on *.* to

root@'localhost' identified by 'root' with grant option;

mysql> grant all on *.* to root@'%' identified by 'root' with

grant option;

mysql> flush privileges;

mysql> show variables like '%version%';

---查看mysql系统版本

5.密码丢失的处理方法

密码丢失,启动时加上参数:skip-grant-tables：

/usr/local/mysql/bin/mysqld_safe

--defaults-file=/etc/my.cnf  --skip-grant-tables

--user=mysql &

然后登陆:

/usr/local/mysql/bin/mysql

再重新给密码,不能用grant all on *.* to root@'localhost' identified

by 'root';会报错.

mysql>

grant all on *.* to root@'localhost' identified by

'root';

ERROR 1290

(HY000): The MySQL server is running with the --skip-grant-tables

option so it cannot execute this statement

使用update更新密码.用函数password().

update

mysql.user set authentication_string=password('root') where

user='root';

#重新载入权限表

flush

privileges;

mysql的配置文件：

1.mysql5.6配置文件：

[client]

port

=

3306

socket

= /tmp/mysql3306.sock

[mysqld]

port

=

3306

socket

= /tmp/mysql3306.sock

datadir

=

/home/mysql3306/mysql3306

#read_only

= on

#--- GLOBAL ---#

lower_case_table_names  = 1

log-output

= FILE

log-error

=

/home/mysql3306/logs/mysql-error.log

#general_log

general_log_file

=

/home/mysql3306/logs/mysql.log

pid-file

=

/home/mysql3306/mysql.pid

slow-query-log

= 1

slow_query_log_file

= /home/mysql3306/logs/mysql-slow.log

tmpdir

=

/tmp/

long_query_time

= 2

innodb_force_recovery   =

0

#innodb_buffer_pool_dump_at_shutdown = 1

#innodb_buffer_pool_load_at_startup = 1

#--------------#

#thread_concurrency

= 8

thread_cache_size

= 51

table_open_cache

= 16384

open_files_limit

= 65535

table_definition_cache  = 16384

sort_buffer_size

= 2M

join_buffer_size

= 2M

read_buffer_size

= 2M

read_rnd_buffer_size    =

8M

key_buffer_size

= 32M

bulk_insert_buffer_size = 16M

myisam_sort_buffer_size = 64M

tmp_table_size

=

32M

max_heap_table_size

= 16M

query_cache_size

= 32MB

#gtid_mode=on

#log_slave_updates=1

#enforce_gtid_consistency=1

#--- NETWORK ---#

back_log

=

103

max-connections

= 512

max_connect_errors

= 100000

max_allowed_packet

= 32M

interactive_timeout

= 600

wait_timeout

= 600

skip-external-locking

#max_user_connections    =

0

external-locking

= FALSE

#skip-name-resolve

#--- REPL ---#

server-id

=

88083306

sync_binlog

= 1

log-bin

= mysql-bin

binlog_format

= row

expire_logs_days

= 10

relay-log

=

relay-log

replicate-ignore-db

= test

log_slave_updates

=1

#skip-slave-start

binlog_cache_size

=4M

max_binlog_cache_size

=8M

max_binlog_size

=1024M

#--- INNODB ---#

default_storage_engine

=

InnoDB

innodb_data_file_path

= ibdata1:1024M:autoextend

innodb_buffer_pool_size

=

800M

innodb_buffer_pool_instances

= 1

innodb_log_files_in_group

= 2

innodb_log_file_size

= 256MB

innodb_log_buffer_size

=

16M

innodb_flush_log_at_trx_commit  = 2

innodb_lock_wait_timeout

= 30

innodb_flush_method

= O_DIRECT

innodb_max_dirty_pages_pct

= 75

innodb_io_capacity

= 200

innodb_thread_concurrency

= 32

innodb_open_files

=

65535

innodb_file_per_table

= 1

transaction_isolation

= REPEATABLE-READ

innodb_locks_unsafe_for_binlog  = 0

#innodb_purge_thread

= 4

skip_name_resolve

= 1

[mysqldump]

quick

max_allowed_packet = 32M

[mysql]

auto-rehash

# Remove the next comment character if you are not familiar

with SQL

#safe-updates

default_character_set=utf8

[mysqlhotcopy]

interactive-timeout

2.mysql5.7配置文件：

[client]

port

= 3306

socket

= /tmp/mysql3306.sock

default_character_set

= utf8mb4

[mysql]

default_character_set

= utf8mb4

[mysqld]

lower_case_table_names

= 1

port

= 3306

basedir

= /usr/local/mysql

datadir

=

/home/mysql3306/mysql3306

socket

= /tmp/mysql3306.sock

pid_file

= /home/mysql3306/mysql3306.pid

tmpdir

= /tmp/

skip_name_resolve

= 1

character_set_server

= utf8mb4

collation_server

=

utf8mb4_unicode_ci

max_connections

= 2000

max_connect_errors

= 10000

interactive_timeout

= 600

wait_timeout

= 600

table_open_cache

=

2048

query_cache_type

= 0

#query_cache_size

= 64M

#query_cache_limit

= 2M

thread_cache_size

= 51

max_allowed_packet

= 16M

tmp_table_size

= 256M

max_tmp_tables

= 128

max_heap_table_size

= 96M

sort_buffer_size

= 4M

read_buffer_size

=

4M

join_buffer_size

= 4M

read_rnd_buffer_size

= 8M

bulk_insert_buffer_size

=

64M

log_error

= /home/mysql3306/logs/mysql-error.log

log_timestamps

= system

slow_query_log

= 1

slow_query_log_file

=

/home/mysql3306/logs/mysql-slow.log

long_query_time

= 1

log_queries_not_using_indexes

= 1

log_throttle_queries_not_using_indexes =10

log_slow_admin_statements

= 1

log_slow_slave_statements

= 1

min_examined_row_limit

=

100

log_queries_not_using_indexes

= 1

log_output

=

FILE

relay_log

= /home/mysql3306/mysql3306/mysql-relay

#binlog

server_id

= 88083306

log_bin

=

/home/mysql3306/mysql3306/mysql-bin

expire_logs_days

= 7

binlog_format

= row

max_binlog_size

= 1024M

max_binlog_cache_size

= 8G

binlog_cache_size

= 4M

sync_binlog

= 1

master_info_repository

=

TABLE

relay_log_info_repository

= TABLE

relay_log_recovery

= ON

log_slave_updates

= 1

#rep

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

#innodb

default_storage_engine

=

InnoDB

innodb_page_size

=

16384

innodb_data_home_dir

= /home/mysql3306/mysql3306/

innodb_data_file_path

= ibdata1:512M:autoextend

innodb_log_group_home_dir

=

/home/mysql3306/mysql3306

innodb_buffer_pool_instances

= 1

innodb_buffer_pool_size

=

600M

innodb_buffer_pool_dump_pct

= 40

innodb_page_cleaners

= 8

innodb_log_file_size

= 256M

innodb_log_files_in_group

= 2

innodb_log_buffer_size

=

32M  #default 16M

innodb_flush_log_at_trx_commit  = 1

innodb_lock_wait_timeout

= 30

innodb_strict_mode

= 1

innodb_print_all_deadlocks

= 1

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_dump_pct

= 40

innodb_open_files

=

65536

innodb_file_per_table

= 1

innodb_lock_wait_timeout

= 30

innodb_read_io_threads

= 8

innodb_write_io_threads

= 8

innodb_io_capacity

= 200

innodb_flush_log_at_trx_commit  = 1

innodb_flush_method

= O_DIRECT

innodb_purge_threads

= 4

innodb_support_xa

= 1

innodb_max_dirty_pages_pct

= 75

transaction_isolation

= READ-COMMITTED

innodb_page_cleaners

= 16

explicit_defaults_for_timestamp = 1

#undo

innodb_undo_directory

= /home/mysql3306/mysql3306/

innodb_undo_logs

=

128

innodb_undo_tablespaces

= 3

innodb_undo_log_truncate

= 1

innodb_max_undo_log_size

= 1000M

innodb_purge_rseg_truncate_frequency = 128

[mysqldump]

max_allowed_packet

= 16M