mysql下载与安装

1.mysql 官网：

https://www.mysql.com/

2.

 

 

 

解压与安装

[root@python ~]# tar xf /usr/local/src/mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz -C /mysql/

[root@python ~]# yum install -y libaio
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Install Process
Loading mirror speeds from cached hostfile
Package libaio-0.3.107-10.el6.x86_64 already installed and latest version
Nothing to do

[root@python mysql-5.6.42-linux-glibc2.12-x86_64]# pwd
/mysql/mysql-5.6.42-linux-glibc2.12-x86_64

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db --user=mysql
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

[root@python mysql]# /etc/init.d/mysql.server start
Starting MySQL.[ OK ]

[root@python mysql]# ps aux|grep mysql
root 8856 0.0 0.1 106228 1396 pts/1 S 10:16 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/python.pid
mysql 9030 2.6 44.9 1340864 451856 pts/1 Sl 10:16 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/python.pid --socket=/var/lib/mysql/mysql.sock
root 9072 0.0 0.0 103308 856 pts/1 S+ 10:17 0:00 grep mysql

 #####################mysql-5.7.9-linux-glibc2.5-x86_64##########

shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 770 mysql-files
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> bin/mysql_install_db --user=mysql    # Before MySQL 5.7.6
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup              # MySQL 5.7.6 and up
shell> chown -R root .
shell> chown -R mysql data mysql-files
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

[root@python mysql]# bin/mysqld --initialize --user=mysql
2019-01-14T02:21:32.283823Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-01-14T02:21:32.628538Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-01-14T02:21:32.746423Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-01-14T02:21:32.839200Z 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: 1c002a64-17a3-11e9-9d40-000c29963117.
2019-01-14T02:21:32.843105Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-01-14T02:21:32.854518Z 1 [Note] A temporary password is generated for root@localhost: w37acwVqTt=O

[root@python ~]# ps -A|grep mysql
 14603 ?        00:00:00 mysqld_safe
 14777 ?        00:01:24 mysqld
[root@python ~]# kill -9 14603
[root@python ~]# kill -9 14777
[root@python ~]# ps -A|grep mysql
[root@python ~]# /etc/init.d/mysql
mysqld        mysql.server  
[root@python ~]# mv /etc/init.d/mysql.server /etc/init.d/mysqld
mv: overwrite `/etc/init.d/mysqld'? yes
[root@python ~]# /etc/init.d/mysqld restart
MySQL server PID file could not be found![FAILED]
Starting MySQL..[  OK  ]
[root@python ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9

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> set password = '123';                ####改密码    set password = password("123456")
  uery OK, 0 rows affected (0.00 sec)

[root@python ~]# mysql -u root -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9 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>

[root@python mysql]# mysqld --help -v|grep 'my.cnf'
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /usr/local/mysql/my.cnf ~/.my.cnf   ###后面的覆盖前面的，覆盖原则
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

###############mysql5.6 升级到5.7 ########################

####################my.cnf 经典配置文件#####################

[client]
user=david
password=88888888

[mysqld]
########basic settings########
server-id = 11 
port = 3306
user = mysql
bind_address = 10.166.224.32
autocommit = 0
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 6G              ###这个参数可以配置到服务器内存总数的75%-90%
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/
innodb_undo_directory = /undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

 

#####################语句###############################

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                6442450944 |
+---------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 6442450944 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.03 sec)

mysql> select autocommit;
ERROR 1054 (42S22): Unknown column 'autocommit' in 'field list'
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

 

 

mysql> grant select on sys.* to 'perf'@'127.0.0.1' identified by '123';   ####官方不推荐直接用grant 创建用户；会有warning
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

标准做法是：

mysql> create user 'perf'@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on sys.* to 'perf'@'127.0.0.1';
Query OK, 0 rows affected (0.02 sec)

这样就不会再有warning!!!!　　

mysql> show grants for 'perf'@'127.0.0.1';
+-----------------------------------------------+
| Grants for perf@127.0.0.1 |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'perf'@'127.0.0.1' |
| GRANT SELECT ON `sys`.* TO 'perf'@'127.0.0.1' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> drop user 'perf'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

 

 

##################创建角色与组的概念###################

通过mysql-proxy的功能实现组的功能

mysql> create user 'junior_dba'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'jim'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'tom'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> grant proxy on 'junior_dba'@'127.0.0.1' to 'jim'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> grant proxy on 'junior_dba'@'127.0.0.1' to 'tom'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on *.* to 'junior_dba'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'junior_dba'@'127.0.0.1';
+-------------------------------------------------+
| Grants for junior_dba@127.0.0.1                 |
+-------------------------------------------------+
| GRANT SELECT ON *.* TO 'junior_dba'@'127.0.0.1' |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'jim'@'127.0.0.1';
+--------------------------------------------------------------+
| Grants for jim@127.0.0.1                                     |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'127.0.0.1'                      |
| GRANT PROXY ON 'junior_dba'@'127.0.0.1' TO 'jim'@'127.0.0.1' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'tom'@'127.0.0.1';
+--------------------------------------------------------------+
| Grants for tom@127.0.0.1                                     |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'127.0.0.1'                      |
| GRANT PROXY ON 'junior_dba'@'127.0.0.1' TO 'tom'@'127.0.0.1' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from proxies_priv;
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root | | | 1 | boot@connecting host | 0000-00-00 00:00:00 |
| 127.0.0.1 | jim | 127.0.0.1 | junior_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
| 127.0.0.1 | tom | 127.0.0.1 | junior_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
3 rows in set (0.00 sec)

 ###############第四天#############################

 

 

 

 

 ####################

[root@python ~]# mysql aaa -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.9-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> show tables;
Empty set (0.00 sec)

mysql> create table a (a int primary key auto_increment);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into a select NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into a select NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

[root@python mysql-utilities-1.6.5]# tar xf mysql-utilities-1.6.5.tar.gz 
[root@python mysql-utilities-1.6.5]# cd mysql-utilities-1.6.5
[root@python mysql-utilities-1.6.5]# python setup.py install

如何查看表结构，不通过show create table table_name：
[root@python ~]# mysqlfrm --diagnostic /usr/local/mysql/data/aaa/a.frm 
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /usr/local/mysql/data/aaa/a.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `aaa`.`a` (
  `a` int(11) NOT NULL AUTO_INCREMENT, 
PRIMARY KEY `PRIMARY` (`a`)
) ENGINE=InnoDB;

#...done.

#####################慢查询日志###############

[root@mysql01 ~]# mysqldumpslow /usr/local/mysql/data/mysql01-slow.log

Reading mysql slow query log from /usr/local/mysql/data/mysql01-slow.log
Count: 2  Time=8.00s (16s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select sleep(N)

########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
log_output = TABLE       #####默认是FILE,slow-log以表的形式存储
min_examined_row_limit = 100    #####这个设置100，意思是，扫描低于100条的SQL语句不会被记录到slow_log,测试的时候，最好设置为0

mysql> show variables like "log_output";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| 2019-01-17 17:54:05 | root[root] @ localhost [] | 00:00:06 | 00:00:00 | 1 | 0 | | 0 | 0 | 0 | select sleep(6) | 1 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
1 row in set (0.00 sec)

mysql> select * from mysql.slow_log\G;
*************************** 1. row ***************************
start_time: 2019-01-17 17:54:05
user_host: root[root] @ localhost []
query_time: 00:00:06
lock_time: 00:00:00
rows_sent: 1
rows_examined: 0
db:
last_insert_id: 0
insert_id: 0
server_id: 0
sql_text: select sleep(6)
thread_id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

 

mysql> select * from slow_log where start_time >= '2019-01-17';
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| 2019-01-17 17:54:05 | root[root] @ localhost [] | 00:00:06   | 00:00:00  |         1 |             0 |    |              0 |         0 |         0 | select sleep(6) |         1 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
1 row in set (0.00 sec)

还没结束：

mysql> show create table slow_log\G;
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'     ####我们会发现slow_log的存储引擎是CSV,这个性能比较差，我们需要修改下
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table slow_log engine = 'myisam';         ####修改引擎，提示慢查询开着，无法修改，要先关闭慢查询
ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled

mysql> set global slow_query_log = 0;    ####关闭慢查询日志
Query OK, 0 rows affected (0.00 sec)

mysql> alter table slow_log engine = 'myisam';   ####再修改引擎为myisam
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> set global slow_query_log = 1;    ####再次打开
Query OK, 0 rows affected (0.00 sec)

 

mysql> show create table slow_log\G
*************************** 1. row ***************************
Table: slow_log
Create Table: CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'    ###修改成功
1 row in set (0.00 sec)

 ##############通用日志##################

通常是不开的，性能下降明显，会下降62%左右！！！！！这个通用日志会记录所有操作，每一条记录！

但是这个日志有什么作用呢？因为他会记录所有操作，包括用户登录失败那些，可以用来做审计日志