Linux MySQL

MySQL数据库

第一条产品线：5.0.xx及升级到5.1.xx的产品系列，这条产品线继续完善与改进其用户体验和性能，同时增加新功能。

第二条产品线：为了更好地整合MySQL AB公司社区和第三方公司开发的新存储引擎，以及吸收新的实现和算法等，从而更好地支持SMP架构，提高性能而做了大量的代码重构。版本编号从5.4.xx开始，目前发展到5.7.xx。

第三条产品线：为了更好地推广MySQL Cluster版本，以及提高MySQL Cluster的性能和稳定性，以及功能改进和增加，以及改动MySQL基础功能，使其对Cluster存储引擎提供更有效的支持与优化。版本号为6.0.xx开始，目前发展到7.1.xx。

安装MySQL

Linux软件的安装方式

1、yum/rpm简单、快，无法定制，

2、编译安装，./configure;make;make install。复杂、数度慢，可定制。

针对mysql，第一条产品线的编译方式5.0—5.1。

mysql5.5以上，编译安装，./cmake;gmake;gmake install。

3、二进制包，解压即用。简单、快，不好定制。

mysql-5.5.32-linux2.6-x86_64.tar.gz

  1. [root@lnmp tools]# ls -l mysql-5.5.32-linux2.6-x86_64.tar.gz
  2. -rw-r--r-- 1 root root 186722932 Feb 25 10:17 mysql-5.5.32-linux2.6-x86_64.tar.gz

创建mysql用户

  1. [root@lnmp tools]# useradd -s /sbin/nologin -M mysql
  2. [root@lnmp tools]# id mysql
  3. uid=503(mysql) gid=503(mysql) groups=503(mysql)

解压

  1. [root@lnmp tools]# tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz
  2. [root@lnmp tools]# ls -ld mysql-5.5.32-linux2.6-x86_64
  3. drwxr-xr-x 13 root root 4096 Feb 25 10:22 mysql-5.5.32-linux2.6-x86_64

移动到安装目录、创建软连接

  1. [root@lnmp tools]# mv mysql-5.5.32-linux2.6-x86_64 /application/mysql-5.5.32
  2. [root@lnmp tools]# cd /application/
  3. [root@lnmp application]# ln -s mysql-5.5.32/ mysql
  4. [root@lnmp application]# ls -l mysql
  5. lrwxrwxrwx 1 root root 13 Feb 25 10:25 mysql -> mysql-5.5.32/

操作到此步骤相当于编译安装make install之后。

初始化数据库

  1. [root@lnmp application]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

授权mysql用户管理mysql

  1. [root@lnmp application]# chown -R mysql:mysql /application/mysql
  2. [root@lnmp application]# ls -ld /application/mysql
  3. lrwxrwxrwx 1 mysql mysql 13 Feb 25 10:25 /application/mysql -> mysql-5.5.32/

出现两个OK表示成功

  1. Installing MySQL system tables...
  2. OK
  3. Filling help tables...
  4. OK

生成MySQL配置文件

  1. [root@lnmp mysql]# cp support-files/my-small.cnf /etc/my.cnf

配置启动MySQL

  1. [root@lnmp mysql]# sed -i "s#/usr/local/mysql#/application/mysql#g" /application/mysql/bin/mysqld_safe

后台启动MySQL

  1. [root@lnmp mysql]# /application/mysql/bin/mysqld_safe &
  2. [root@lnmp mysql]# lsof -i:3306
  3. COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
  4. mysqld 1455 mysql 10u IPv4 16888 0t0 TCP *:mysql (LISTEN)

配置环境变量

  1. [root@lnmp mysql]# vim /etc/profile
  2. PATH="/application/mysql/bin:$PATH"
  3. [root@lnmp mysql]# source /etc/profile
  4. [root@lnmp mysql]# which mysql
  5. /application/mysql/bin/mysql

登录测试

  1. [root@lnmp mysql]# mysql
  2. Welcome to the MySQL monitor. Commands end with ; or \g.
  3. Your MySQL connection id is 2
  4. Server version: 5.5.32 MySQL Community Server (GPL)
  5.  
  6. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
  7.  
  8. Oracle is a registered trademark of Oracle Corporation and/or its
  9. affiliates. Other names may be trademarks of their respective
  10. owners.
  11.  
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13.  
  14. mysql>

配置传统方式启动MySQL

  1. [root@lnmp mysql]# cp support-files/mysql.server /etc/init.d/mysqld
  2. [root@lnmp mysql]# sed -i "s#/usr/local/mysql#/application/mysql#g" /etc/init.d/mysqld
  3. [root@lnmp mysql]# chmod +x /etc/init.d/mysqld
  4. [root@lnmp mysql]# killall mysqld
  5. [root@lnmp mysql]# lsof -i:3306
  6. [root@lnmp mysql]# /etc/init.d/mysqld start
  7. Starting MySQL.. SUCCESS!
  8. [root@lnmp mysql]# lsof -i:3306
  9. COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
  10. mysqld 6993 mysql 10u IPv4 21591 0t0 TCP *:mysql (LISTEN)

设置密码

  1. [root@lnmp mysql]# mysqladmin -uroot password "system"
  2. [root@lnmp mysql]# mysql
  3. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  4. [root@lnmp mysql]# mysql -uroot -psystem
  5. Welcome to the MySQL monitor. Commands end with ; or \g.
  6. Your MySQL connection id is 3
  7. Server version: 5.5.32 MySQL Community Server (GPL)
  8.  
  9. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
  10.  
  11. Oracle is a registered trademark of Oracle Corporation and/or its
  12. affiliates. Other names may be trademarks of their respective
  13. owners.
  14.  
  15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  16.  
  17. mysql>

修改密码

  1. [root@lnmp mysql]# mysqladmin -uroot -psystem password "123456"
  2. [root@lnmp mysql]# mysql -uroot -psystem
  3. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  4. [root@lnmp mysql]# mysql -uroot -p123456
  5. Welcome to the MySQL monitor. Commands end with ; or \g.
  6. Your MySQL connection id is 7
  7. Server version: 5.5.32 MySQL Community Server (GPL)
  8.  
  9. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
  10.  
  11. Oracle is a registered trademark of Oracle Corporation and/or its
  12. affiliates. Other names may be trademarks of their respective
  13. owners.
  14.  
  15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  16.  
  17. mysql>

交互式登录mysql

  1. [root@lnmp mysql]# mysql -uroot -p
  2. Enter password:
  3. mysql> show databases; #查看所有库
  4. +--------------------+
  5. | Database |
  6. +--------------------+
  7. | information_schema |
  8. | mysql |
  9. | performance_schema |
  10. | test |
  11. +--------------------+
  12. 4 rows in set (0.00 sec)
  13.  
  14. mysql> drop database test; #删除test表
  15. Query OK, 0 rows affected (0.04 sec)
  16.  
  17. mysql> show databases;
  18. +--------------------+
  19. | Database |
  20. +--------------------+
  21. | information_schema |
  22. | mysql |
  23. | performance_schema |
  24. +--------------------+
  25. 3 rows in set (0.00 sec)
  26. mysql> select user,host from mysql.user;
  27. +------+-----------+
  28. | user | host |
  29. +------+-----------+
  30. | root | 127.0.0.1 |
  31. | root | ::1 |
  32. | | lnmp |
  33. | root | lnmp |
  34. | | localhost |
  35. | root | localhost |
  36. +------+-----------+
  37. 6 rows in set (0.03 sec)
  38. mysql> drop user ''@'lnmp'; #删除用户
  39. Query OK, 0 rows affected (0.05 sec)
  40. mysql> drop user ''@'localhost';
  41. Query OK, 0 rows affected (0.00 sec)
  42. mysql> drop user 'root'@'lnmp';
  43. Query OK, 0 rows affected (0.00 sec)
  44. mysql> drop user 'root'@'::1';
  45. Query OK, 0 rows affected (0.00 sec)
  46. mysql> select user,host from mysql.user;
  47. +------+-----------+
  48. | user | host |
  49. +------+-----------+
  50. | root | 127.0.0.1 |
  51. | root | localhost |
  52. +------+-----------+
  53. 2 rows in set (0.00 sec)

当有特殊字符或大写时，使用delete删除。

  1. mysql> delete from mysql.user where user="root" and host="A";
  2. Query OK, 0 rows affected (0.05 sec)

最后执行刷新，让权限生效。

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

查所有的库：show databases;

切库：use mysql;

查表：show tables;

查看用户列表：select user,host from mysql.user;

查看当前用户：select user();

查看当前所在库：select database();

删除数据库：drop database 库名;

删除用户：drop user '用户名'@'主机';

posted on 2017-02-25 15:43  yinshoucheng  阅读(349)  评论(0)  编辑  收藏  举报

导航