当前位置:Gxlcms > 数据库问题 > MySQL 系列(一) 生产标准线上环境安装配置案例及棘手问题解决

MySQL 系列(一) 生产标准线上环境安装配置案例及棘手问题解决

时间:2021-07-01 10:21:17 帮助过:12人阅读

  1. 1. sudo apt-get install mysql-<span style="color: #000000;">server
  2. </span>2. sudo apt-get isntall mysql-<span style="color: #000000;">client
  3. </span>3. sudo apt-get install libmysqlclient-<span style="color: #000000;">dev
  4. # 检测是否安装成功(是否为LISTEN状态)
  5. sudo netstat </span>-tap | grep mysql
技术图片

编译安装MySQL-5.5.32:

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;"> 安装依赖包</span>
  2. yum install ncurses-devel gcc gcc-c++ -<span style="color: #000000;">y
  3. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 创建目录</span>
  4. mkdir -p /home/oldsuo/<span style="color: #000000;">tools
  5. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 安装cmake软件,gmake编译安装</span>
  6. cd /home/oldsuo/tools/<span style="color: #000000;">
  7. tar xf cmake</span>-2.8.8<span style="color: #000000;">.tar.gz
  8. cd cmake</span>-2.8.8<span style="color: #000000;">
  9. .</span>/<span style="color: #000000;">configure
  10. </span><span style="color: #008000;">#</span><span style="color: #008000;">CMake has bootstrapped. Now run gmake.</span>
  11. <span style="color: #000000;">gmake
  12. gmake install
  13. cd ..</span>/
  14. <span style="color: #008000;">#</span><span style="color: #008000;"> 开始安装mysql</span><span style="color: #008000;">
  15. #</span><span style="color: #008000;"> 创建用户和组</span>
  16. <span style="color: #000000;">groupadd mysql
  17. useradd mysql </span>-s /sbin/nologin -M -<span style="color: #000000;">g mysql
  18. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 解压编译MySQL</span>
  19. tar zxf mysql-5.5.32<span style="color: #000000;">.tar.gz
  20. cd mysql</span>-5.5.32<span style="color: #000000;">
  21. cmake . </span>-DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32<span style="color: #000000;"> </span>-DMYSQL_DATADIR=/application/mysql-5.5.32/<span style="color: #000000;">data </span>-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/<span style="color: #000000;">mysql.sock </span>-DDEFAULT_CHARSET=<span style="color: #000000;">utf8 </span>-DDEFAULT_COLLATION=<span style="color: #000000;">utf8_general_ci </span>-DEXTRA_CHARSETS=<span style="color: #000000;">gbk,gb2312,utf8,ascii </span>-DENABLED_LOCAL_INFILE=<span style="color: #000000;">ON </span>-DWITH_INNOBASE_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITH_FEDERATED_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITH_BLACKHOLE_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITHOUT_PARTITION_STORAGE_ENGINE=1<span style="color: #000000;"> </span>-DWITH_FAST_MUTEXES=1<span style="color: #000000;"> </span>-DWITH_ZLIB=<span style="color: #000000;">bundled </span>-DENABLED_LOCAL_INFILE=1<span style="color: #000000;"> </span>-DWITH_READLINE=1<span style="color: #000000;"> </span>-DWITH_EMBEDDED_SERVER=1<span style="color: #000000;"> </span>-DWITH_DEBUG=<span style="color: #000000;">0
  22. </span><span style="color: #008000;">#</span><span style="color: #008000;">-- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32</span>
  23. <span style="color: #000000;">提示: 编译时可配置的选项很多,具体可参考结尾附录或官方文档:
  24. make
  25. </span><span style="color: #008000;">#</span><span style="color: #008000;">[100%] Built target my_safe_process</span>
  26. <span style="color: #000000;">make install
  27. ln </span>-s /application/mysql-5.5.32/ /application/<span style="color: #000000;">mysql
  28. 如果上述操作未出现错误,则MySQL5.</span>5<span style="color: #000000;">.32软件cmake方式的安装就算成功了。
  29. </span><span style="color: #008000;">#</span><span style="color: #008000;">拷贝配置文件</span>
  30. cp mysql-5.5.32/support-files/my-small.cnf /etc/<span style="color: #000000;">my.cnf
  31. </span><span style="color: #008000;">#</span><span style="color: #008000;">添加变量,并使之生效</span>
  32. echo <span style="color: #800000;">‘</span><span style="color: #800000;">export PATH=/application/mysql/bin:$PATH</span><span style="color: #800000;">‘</span> >>/etc/<span style="color: #000000;">profile
  33. source </span>/etc/<span style="color: #000000;">profile
  34. echo $PATH
  35. </span><span style="color: #008000;">#</span><span style="color: #008000;">授权用户及/tmp/临时文件目录</span>
  36. chown -R mysql.mysql /application/mysql/data/<span style="color: #000000;">
  37. chmod </span>-R 1777 /tmp/
  38. <span style="color: #008000;">#</span><span style="color: #008000;">初始化数据库</span>
  39. cd /application/mysql/scripts/<span style="color: #000000;">
  40. .</span>/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=<span style="color: #000000;">mysql
  41. cd ..</span>/
  42. <span style="color: #008000;">#</span><span style="color: #008000;">启动数据库</span>
  43. cp support-files/mysql.server /etc/init.d/<span style="color: #000000;">mysqld
  44. chmod </span>+x /etc/init.d/<span style="color: #000000;">mysqld
  45. </span>/etc/init.d/<span style="color: #000000;">mysqld start
  46. </span><span style="color: #008000;">#</span><span style="color: #008000;">检查端口</span>
  47. netstat -lntup|grep 3306
技术图片

 编译安装完后一般安全操作:

 1、删除不必要的用户和库:

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;">查看用户和主机列,从mysql.user里查看</span>
  2. select user,host <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
  3. </span><span style="color: #008000;">#</span><span style="color: #008000;">删除用户名为空的库,并检查</span>
  4. delete <span style="color: #0000ff;">from</span> mysql.user where user=<span style="color: #800000;">‘‘</span><span style="color: #000000;">;
  5. select user,host </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
  6. </span><span style="color: #008000;">#</span><span style="color: #008000;">删除主机名为localhost.localdomain的库,并检查</span>
  7. delete <span style="color: #0000ff;">from</span> mysql.user where host=<span style="color: #800000;">‘</span><span style="color: #800000;">localhost.localdomain</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  8. select user,host </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
  9. </span><span style="color: #008000;">#</span><span style="color: #008000;">删除主机名为::1的库,并检查。::1库的作用为IPV6</span>
  10. delete <span style="color: #0000ff;">from</span> mysql.user where host=<span style="color: #800000;">‘</span><span style="color: #800000;">::1</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  11. </span><span style="color: #008000;">#</span><span style="color: #008000;">删除test库</span>
  12. drop database test;
技术图片

2、添加额外管理员:

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;"> 添加额外管理员,system作为管理员,oldsuo为密码</span>
  2. mysql> delete <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
  3. Query OK, </span>2 rows affected (0.00<span style="color: #000000;"> sec)
  4. mysql</span>> grant all privileges on *.* to system@<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span><span style="color: #000000;"> with grant option;
  5. Query OK, 0 rows affected (</span>0.00<span style="color: #000000;"> sec)
  6. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 刷新MySQL的系统权限相关表,使配置生效</span>
  7. mysql><span style="color: #000000;"> flush privileges;
  8. Query OK, 0 rows affected (</span>0.00<span style="color: #000000;"> sec)
  9. mysql</span>> select user,host <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
  10. </span>+--------+-----------+
  11. | user | host |
  12. +--------+-----------+
  13. | system | localhost |
  14. +--------+-----------+
  15. 1 row <span style="color: #0000ff;">in</span> set (0.00<span style="color: #000000;"> sec)
  16. mysql</span>>
技术图片

3、设置登录密码并开机自启:

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;">设置密码,并登陆</span>
  2. /usr/local/mysql/bin/mysqladmin -u root password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  3. mysql </span>-usystem -<span style="color: #000000;">p
  4. </span><span style="color: #008000;">#</span><span style="color: #008000;">开机启动mysqld,并检查</span>
  5. <span style="color: #000000;">chkconfig mysqld on
  6. chkconfig </span>--list mysqld
技术图片

 

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;">安装依赖包</span>
  2. yum –y install ncurses ncurses-devel gcc gcc-c++
  3. <span style="color: #008000;">#</span><span style="color: #008000;">添加mysql用户及组</span>
  4. <span style="color: #000000;">groupadd mysql
  5. useradd </span>-r -s /sbin/nologin -<span style="color: #000000;">g mysql mysql
  6. </span><span style="color: #008000;">#</span><span style="color: #008000;">mysql5.1.62编译参数:</span>
  7. ./<span style="color: #000000;">configure </span>--prefix=/usr/local/<span style="color: #000000;">mysql </span>--with-unix-soket-path=/usr/local/tmp/<span style="color: #000000;">mysql.sock </span>--localstatedir=/usr/local/mysql/<span style="color: #000000;">data </span>--enable-<span style="color: #000000;">assembler </span>--enable-thread-safe-<span style="color: #000000;">client </span>--with-mysqld-user=<span style="color: #000000;">mysql </span>--with-big-<span style="color: #000000;">tables </span>--without-<span style="color: #000000;">debug </span>--with-<span style="color: #000000;">pthread </span>--enable-<span style="color: #000000;">assembler </span>--with-extra-charsets=<span style="color: #000000;">complex </span>--with-<span style="color: #000000;">readline </span>--with-<span style="color: #000000;">ssl </span>--with-embedded-<span style="color: #000000;">server </span>--enable-local-<span style="color: #000000;">infile </span>--with-plugins=<span style="color: #000000;">partition,innobase </span>--with-mysqld-ldflags=-all-<span style="color: #000000;">static </span>--with-client-ldflags=-all-<span style="color: #000000;">static
  8. make </span>&&<span style="color: #000000;"> make install
  9. </span><span style="color: #008000;">#</span><span style="color: #008000;">初始化mysql</span>
  10. mkdir -p /usr/local/mysql/data <span style="color: #008000;">#</span><span style="color: #008000;">建立mysql数据文件目录</span>
  11. chown -R mysql.mysql /usr/local/mysql/ <span style="color: #008000;">#</span><span style="color: #008000;">授权mysql用户访问mysql安装目录</span>
  12. /usr/local/mysql/bin/mysql_install_db --user=mysql <span style="color: #008000;">#</span><span style="color: #008000;">初始化</span>
  13. <span style="color: #008000;">#</span><span style="color: #008000;">拷贝mysql启动脚本</span>
  14. cp support-files/my-small.cnf /etc/<span style="color: #000000;">my.cnf
  15. </span><span style="color: #008000;">#</span><span style="color: #008000;">cp support-files/mysql.server /etc/init.d/mysqld </span>
  16. chmod 700 /etc/init.d/<span style="color: #000000;">mysqld
  17. </span><span style="color: #008000;">#</span><span style="color: #008000;">配置mysql使用全局路径</span>
  18. echo <span style="color: #800000;">‘</span><span style="color: #800000;">export PATH=/application/mysql/bin:$PATH</span><span style="color: #800000;">‘</span> >>/etc/profile <span style="color: #008000;">#</span><span style="color: #008000;">添加变量到profile</span>
  19. source /etc/profile <span style="color: #008000;">#</span><span style="color: #008000;">使变量生效</span>
  20. echo $PATH <span style="color: #008000;">#</span><span style="color: #008000;">检查</span>
  21. <span style="color: #008000;">#</span><span style="color: #008000;">启动mysqld</span>
  22. /etc/init.d/<span style="color: #000000;">mysqld start
  23. </span><span style="color: #008000;">#</span><span style="color: #008000;">登陆报错,做软链接</span><span style="color: #008000;">
  24. #</span><span style="color: #008000;">ln -s /usr/local/mysql/bin/mysql /usr/bin/</span>
  25. <span style="color: #008000;">#</span><span style="color: #008000;">启动报错日志: Fatal error: Can‘t open and lock privilege tables: Table ‘mysql.host‘ doesn‘t #exist</span><span style="color: #008000;">
  26. #</span><span style="color: #008000;">解决方法: /usr/local/mysql/bin/mysql_install_db --user=mysql #初始化数据库即可</span>
  27. <span style="color: #008000;">#</span><span style="color: #008000;">登陆报错: mysql: unknown variable ‘datadir=/usr/local/mysql/data‘</span><span style="color: #008000;">
  28. #</span><span style="color: #008000;">解决方法: my.cnf 配置问题,vim /etc/my.cnf</span>
  29. <span style="color: #000000;">[client]
  30. </span><span style="color: #008000;">#</span><span style="color: #008000;">password = your_password</span>
  31. port = 3306<span style="color: #000000;">
  32. socket </span>= /tmp/<span style="color: #000000;">mysql.sock
  33. </span><span style="color: #008000;">#</span><span style="color: #008000;">datadir = /data1/mysql/var/ #这个不能加在上面,去掉</span>
  34. <span style="color: #000000;">
  35. [mysqld]
  36. port </span>= 3306<span style="color: #000000;">
  37. socket </span>= /tmp/<span style="color: #000000;">mysql.sock
  38. datadir </span>= /data1/mysql/var/ <span style="color: #008000;">#</span><span style="color: #008000;">加在这里就可以了</span>
  39. <span style="color: #008000;">#</span><span style="color: #008000;">设置mysql用户root 的密码为oldsuo</span>
  40. /usr/local/mysql/bin/mysqladmin -u root password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span>
mysql5.1.62安装编译

 

 三、字符集

 对于新手来说,字符集乱码问题无疑是头痛的问题,小编就带你不在头痛,从此幸福。

1、字符集简介:

字符集,character set,就是一套表示字符的符号和这些的符号的底层编码;而校验规则,则是在字符集内用于比较字符的一套规则。简单的说,字符集就是一套文字符号及其编码、比较规则的集合,第一个计算机字符集ASC2,MySQL数据库字符集包括字符集和校对规则两个概念,字符集是定义数据库里面的内容字符串的存储方式,而校对规则是定义比较字符串的方式。

建议:中英文环境选择utf8

2、查看设置字符集

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;"> 查看MySQL字符集设置情况</span>
  2. show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">character_set%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查看库的字符集</span>
  4. <span style="color: #000000;">show create database db;
  5. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查看表的字符集</span>
  6. <span style="color: #000000;">show create table db_tb\G
  7. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查询所有</span>
  8. <span style="color: #000000;">show collation;
  9. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 设置表的字符集</span>
  10. set tables utf8;
技术图片 技术图片
  1. show create database nick_defailt\G <span style="color: #008000;">#</span><span style="color: #008000;">查看nick_defailt库字符集</span>
  2. mysql -uroot -p -e <span style="color: #800000;">"</span><span style="color: #800000;">SHOW CHARACTER SET;</span><span style="color: #800000;">"</span><span style="color: #000000;">
  3. show variables like </span><span style="color: #800000;">‘</span><span style="color: #800000;">character_set%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  4. mysql</span>> show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">character_set%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  5. </span>+-----------------------------------------+------------------------------------------------------------+
  6. | Variable_name | Value |
  7. +----------------------------------------+-------------------------------------------------------------+
  8. | character_set_client | utf8 |
  9. | character_set_connection | utf8 |
  10. | character_set_database | utf8 |
  11. | character_set_filesystem | binary |
  12. | character_set_results | utf8 |
  13. | character_set_server | utf8 |
  14. | character_set_system | utf8 |
  15. | character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
  16. +----------------------------------------+--------------------------------------------------------------+
  17. 8 rows <span style="color: #0000ff;">in</span> set (0.00<span style="color: #000000;"> sec)
  18. mysql</span>><span style="color: #000000;"> show create database nick_defailt \G
  19. </span>*************************** 1. row ***************************<span style="color: #000000;">
  20. Database: data
  21. Create Database: CREATE DATABASE `data` </span>/*!40100 DEFAULT CHARACTER SET utf8 */
  22. 1 row <span style="color: #0000ff;">in</span> set (0.00 sec)
View Code

3、MySQL数据乱码及解决方法

技术图片
  1. 1><span style="color: #000000;"> 系统方面
  2. cat </span>/etc/sysconfig/<span style="color: #000000;">i18n
  3. LANG</span>=<span style="color: #800000;">"</span><span style="color: #800000;">zh_CN.UTF-8</span><span style="color: #800000;">"</span>
  4. 2><span style="color: #000000;"> 客户端(程序),调整字符集为latin1。
  5. mysql</span>> set names latin1; <span style="color: #008000;">#</span><span style="color: #008000;">临时生效</span>
  6. Query OK, 0 rows affected (0.00<span style="color: #000000;"> sec)
  7. </span><span style="color: #008000;">#</span><span style="color: #008000;">更改my.cnf客户端模块的参数,实现set name latin1 的效果,并且永久生效。</span>
  8. <span style="color: #000000;"> [client]
  9. default</span>-character-set=<span style="color: #000000;">latin1
  10. </span><span style="color: #008000;">#</span><span style="color: #008000;">无需重启服务,退出登录就生效,相当于set name latin1。</span>
  11. 3><span style="color: #000000;"> 服务端,更改my.cnf参数
  12. [mysqld]
  13. default</span>-character-set=latin1 <span style="color: #008000;">#</span><span style="color: #008000;">适合5.1及以前版本</span>
  14. character-set-server=latin1 <span style="color: #008000;">#</span><span style="color: #008000;">适合5.5</span>
  15. 4><span style="color: #000000;"> 库、表、程序
  16. </span><span style="color: #008000;">#</span><span style="color: #008000;">建表指定utf8字符集</span>
  17. mysql><span style="color: #000000;"> create database nick_defailtsss DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  18. Query OK, </span>1 row affected (0.00 sec)
技术图片

4、将utf8字符集修改成GBK字符集的实际过程

技术图片
  1. 1><span style="color: #000000;"> 导出表结构
  2. </span><span style="color: #008000;">#</span><span style="color: #008000;">以utf8格式导出</span>
  3. mysqldump -uroot -p --default-character-set=utf8 -d nick_defailt><span style="color: #000000;">alltable.sql
  4. </span>--default-character-set=gbk <span style="color: #008000;">#</span><span style="color: #008000;">表示已GBK字符集连接 –d 只表示表结构</span>
  5. 2><span style="color: #000000;"> 编辑alltable.sql 将utf8改成gbk。
  6. </span>3><span style="color: #000000;"> 确保数据库不在更新,导出所有数据
  7. mysqldump </span>-uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 nick_defailt><span style="color: #000000;">alldata.sql
  8. </span>4><span style="color: #000000;"> 打开alldata.sql将set name utf8 修改成 set names gbk(或者修改系统的服务端和客户端)
  9. </span>5><span style="color: #000000;"> 建库
  10. create database oldsuo default charset gbk;
  11. </span>6><span style="color: #000000;"> 创建表,执行alltable.sql
  12. mysql </span>-uroot -p oldsuo <<span style="color: #000000;">alltable.sql
  13. </span>7><span style="color: #000000;"> 导入数据
  14. mysql </span>-uroot -p oldsuo <alltable.sql
技术图片

 

 四、存储引擎

MySQL最常用存储引擎Myisam和Innodb。mysql 5.5.5以后默认存储引擎为Innodb。

MySQL的每种引擎在MySQL里是通过插件的方式使用的,MySQL可以支持多种存储引擎。

建议:使用 Innodb引擎,因为支持回滚,后续博客会讲。

1、引擎对应系统文件

技术图片
  1. 1<span style="color: #000000;">) MyISAM引擎系统库表对应文件
  2. [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> ll /data/3306/data/mysql/</span>
  3. -rw-rw----. 1 mysql mysql 10630 10月 31 16:05 user.frm <span style="color: #008000;">#</span><span style="color: #008000;">保存表的定义</span>
  4. -rw-rw----. 1 mysql mysql 1140 10月 31 18:40 user.MYD <span style="color: #008000;">#</span><span style="color: #008000;">数据文件</span>
  5. -rw-rw----. 1 mysql mysql 2048 10月 31 18:40 user.MYI <span style="color: #008000;">#</span><span style="color: #008000;">索引文件</span>
  6. <span style="color: #000000;">
  7. [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> file data/mysql/user.frm </span>
  8. data/mysql/user.frm: MySQL table definition file Version 9<span style="color: #000000;">
  9. [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> file data/mysql/user.MYD </span>
  10. data/mysql/user.MYD: DBase 3 data file (167514107<span style="color: #000000;"> records)
  11. [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> file data/mysql/user.MYI</span>
  12. data/mysql/user.MYI: MySQL MISAM compressed data file Version 1
  13. 2<span style="color: #000000;">) InnoDB引擎
  14. [root@mysql </span>3306]<span style="color: #008000;">#</span><span style="color: #008000;"> ll data/</span>
  15. -rw-rw----. 1 mysql mysql 134217728 10月 31 20:05 ibdata1
技术图片

2、修改引擎

  1. <span style="color: #000000;">创建后引擎的修改
  2. 语法: ALTER TABLE student ENGINE </span>=<span style="color: #000000;"> INNODB;
  3. ALTER TABLE student ENGINE </span>= MyISAM;
技术图片
  1. mysql><span style="color: #000000;"> use teacher;
  2. Database changed
  3. mysql</span>><span style="color: #000000;"> show create table student;
  4. </span>+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Table | Create Table |
  6. +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  7. | student |<span style="color: #000000;"> CREATE TABLE `student` (
  8. `id` int(</span>4<span style="color: #000000;">) NOT NULL AUTO_INCREMENT,
  9. `name` char(</span>20<span style="color: #000000;">) NOT NULL,
  10. `age` tinyint(</span>2) NOT NULL DEFAULT <span style="color: #800000;">‘</span><span style="color: #800000;">0</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  11. `dept` varchar(</span>16<span style="color: #000000;">) DEFAULT NULL,
  12. PRIMARY KEY (`id`),
  13. KEY `index_name` (`name`)
  14. ) ENGINE</span>=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
  15. +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. 1 row <span style="color: #0000ff;">in</span> set (0.01<span style="color: #000000;"> sec)
  17. mysql</span>> ALTER TABLE student ENGINE =<span style="color: #000000;"> MyISAM;
  18. Query OK, </span>3 rows affected (0.05<span style="color: #000000;"> sec)
  19. Records: </span>3<span style="color: #000000;"> Duplicates: 0 Warnings: 0
  20. mysql</span>><span style="color: #000000;"> show create table student;
  21. </span>+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  22. | Table | Create Table |
  23. +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  24. | student |<span style="color: #000000;"> CREATE TABLE `student` (
  25. `id` int(</span>4<span style="color: #000000;">) NOT NULL AUTO_INCREMENT,
  26. `name` char(</span>20<span style="color: #000000;">) NOT NULL,
  27. `age` tinyint(</span>2) NOT NULL DEFAULT <span style="color: #800000;">‘</span><span style="color: #800000;">0</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  28. `dept` varchar(</span>16<span style="color: #000000;">) DEFAULT NULL,
  29. PRIMARY KEY (`id`),
  30. KEY `index_name` (`name`)
  31. ) ENGINE</span>=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
  32. +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  33. 1 row <span style="color: #0000ff;">in</span> set (0.00 sec)
修改实例

3、建表指定引擎

技术图片
  1. mysql><span style="color: #000000;"> create table mess (
  2. </span>-> id int(4) <span style="color: #0000ff;">not</span><span style="color: #000000;"> null,
  3. </span>-> name char(20) <span style="color: #0000ff;">not</span><span style="color: #000000;"> null,
  4. </span>-> age tinyint(2) NOT NULL default <span style="color: #800000;">‘</span><span style="color: #800000;">0</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  5. </span>-> dept varchar(16<span style="color: #000000;">) default NULL
  6. </span>-> ) ENGINE=MyISAM CHARSET=<span style="color: #000000;">utf8;
  7. Query OK, 0 rows affected (</span>0.00 sec)
技术图片

 

 五、基本语句命令

 运行相关:

技术图片
  1. 1<span style="color: #000000;">、 单实例mysql启动
  2. [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> /etc/init.d/mysqld start</span>
  3. <span style="color: #000000;">Starting MySQL [确定]
  4. </span><span style="color: #008000;">#</span><span style="color: #008000;">mysqld_safe –user=mysql &</span>
  5. 2<span style="color: #000000;">、 查看MySQL端口
  6. [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> ss -lntup|grep 3306</span>
  7. tcp LISTEN 0 50 *:3306 *:* users:((<span style="color: #800000;">"</span><span style="color: #800000;">mysqld</span><span style="color: #800000;">"</span>,19651,10<span style="color: #000000;">))
  8. </span>3<span style="color: #000000;">、 查看MySQL进程
  9. [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> ps -ef|grep mysql|grep -v grep</span>
  10. root 19543 1 0 Oct10 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/<span style="color: #000000;">localhost.localdomain.pid
  11. mysql </span>19651 19543 0 Oct10 ? 00:05:04 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
  12. 4<span style="color: #000000;">、 MySQL启动原理
  13. </span>/etc/init.d/<span style="color: #000000;">mysqld 是一个shell启动脚本,启动后最终会调用mysqld_safe脚本,最后调用mysqld服务启动mysql。
  14. </span><span style="color: #800000;">"</span><span style="color: #800000;">$manager</span><span style="color: #800000;">"</span><span style="color: #000000;"> </span>--mysqld-safe-<span style="color: #000000;">compatible </span>--user=<span style="color: #800000;">"</span><span style="color: #800000;">$user</span><span style="color: #800000;">"</span><span style="color: #000000;"> </span>--pid-file=<span style="color: #800000;">"</span><span style="color: #800000;">$pid_file</span><span style="color: #800000;">"</span> >/dev/null 2>&1 &
  15. 5<span style="color: #000000;">、关闭数据库
  16. [root@localhost </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> /etc/init.d/mysqld stop</span>
  17. <span style="color: #000000;">Shutting down MySQL.... [确定]
  18. </span>6<span style="color: #000000;">、 查看mysql数据库里操作命令历史
  19. cat </span>/root/<span style="color: #000000;">.mysql_history
  20. </span>7<span style="color: #000000;">、 强制linux不记录敏感历史命令
  21. HISTCONTROL</span>=<span style="color: #000000;">ignorespace
  22. </span>8<span style="color: #000000;">、 mysql设置密码
  23. </span>/usr/local/mysql/bin/mysqladmin -u root password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span>
  24. 9<span style="color: #000000;">、 mysql修改密码,与多实例指定sock修改密码
  25. mysqladmin </span>-uroot -passwd password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  26. mysqladmin </span>-uroot -passwd password <span style="color: #800000;">‘</span><span style="color: #800000;">oldsuo</span><span style="color: #800000;">‘</span> -S /data/3306/mysql.sock
技术图片

操作相关:

技术图片
  1. <span style="color: #008000;">#</span><span style="color: #008000;">登陆mysql数据库</span>
  2. mysql -<span style="color: #000000;">uroot –p
  3. </span><span style="color: #008000;">#</span><span style="color: #008000;">查看有哪些库</span>
  4. <span style="color: #000000;">show databases;
  5. </span><span style="color: #008000;">#</span><span style="color: #008000;">删除test库</span>
  6. <span style="color: #000000;">drop database test;
  7. </span><span style="color: #008000;">#</span><span style="color: #008000;">使用test库</span>
  8. <span style="color: #000000;">use test;
  9. </span><span style="color: #008000;">#</span><span style="color: #008000;">查看有哪些表</span>
  10. <span style="color: #000000;">show tables;
  11. </span><span style="color: #008000;">#</span><span style="color: #008000;">查看suoning表的所有内容</span>
  12. select * <span style="color: #0000ff;">from</span><span style="color: #000000;"> suoning;
  13. </span><span style="color: #008000;">#</span><span style="color: #008000;">查看当前版本</span>
  14. <span style="color: #000000;">select version();
  15. </span><span style="color: #008000;">#</span><span style="color: #008000;">查看当前用户</span>
  16. <span style="color: #000000;">select user();
  17. </span><span style="color: #008000;">#</span><span style="color: #008000;">查看用户和主机列,从mysql.user里查看</span>
  18. select user,host <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user;
  19. </span><span style="color: #008000;">#</span><span style="color: #008000;">删除前为空,后为localhost的库</span>
  20. drop user <span style="color: #800000;">""</span><span style="color: #000000;">@localhost;
  21. </span><span style="color: #008000;">#</span><span style="color: #008000;">刷新权限</span>
  22. <span style="color: #000000;">flush privileges;
  23. </span><span style="color: #008000;">#</span><span style="color: #008000;">跳出数据库执行命令</span>
  24. system ls;
技术图片

 

 六、破解mysql登录密码

忘记mysql登录密码也是一件头疼的事,那么小编会让你继续幸福。

技术图片
  1. 1> 普通方式
  2. <span style="color: #008000;">#</span><span style="color: #008000;">> service mysqld stop</span><span style="color: #008000;">
  3. #</span><span style="color: #008000;">>mysqld_safe --skip-grant-tables &</span>
  4. 输入 mysql -uroot -<span style="color: #000000;">p 回车进入
  5. </span>><span style="color: #000000;">use mysql;
  6. </span>> update user set password=PASSWORD(<span style="color: #800000;">"</span><span style="color: #800000;">newpass</span><span style="color: #800000;">"</span>)where user=<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span><span style="color: #000000;">;
  7. 更改密码为 newpassord
  8. </span>><span style="color: #000000;"> flush privileges; 更新权限
  9. </span>><span style="color: #000000;"> quit 退出
  10. service mysqld restart
  11. mysql </span>-uroot -<span style="color: #000000;">p新密码进入<br>
  12. </span>2> 普通方式的简写<span style="color: #000000;">
  13. service mysqld stop
  14. mysqld_safe </span>--skip-grant-tables --user=mysql &<span style="color: #000000;">
  15. mysql
  16. update mysql.user set password</span>=PASSWORD(<span style="color: #800000;">"</span><span style="color: #800000;">newpass</span><span style="color: #800000;">"</span>)where user=<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span> <span style="color: #0000ff;">and</span> host=<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  17. flush privileges;
  18. mysqladmin </span>-uroot -<span style="color: #000000;">pnewpass shutdown
  19. </span>/etc/init.d/<span style="color: #000000;">mysqld start
  20. mysql </span>-uroot -pnewpass <span style="color: #008000;">#</span><span style="color: #008000;">登陆</span>
  21. 3><span style="color: #000000;">多实例方式
  22. killall mysqld
  23. mysqld_safe –defaults</span>-file=/data/3306/my.cnf –skip-grant-table &<span style="color: #000000;">
  24. mysql –u root –p –S </span>/data/3306/mysql.sock <span style="color: #008000;">#</span><span style="color: #008000;">指定sock登陆</span>
  25. update mysql.user set password=PASSWORD(<span style="color: #800000;">"</span><span style="color: #800000;">newpass</span><span style="color: #800000;">"</span>)where user=<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span><span style="color: #000000;">;
  26. flush privileges;
  27. mysqladmin </span>-uroot -<span style="color: #000000;">pnewpass shutdown
  28. </span>/etc/init.d/<span style="color: #000000;">mysqld start
  29. mysql </span>-uroot -pnewpass <span style="color: #008000;">#</span><span style="color: #008000;">登陆</span>
技术图片

 

 注:本文有看不懂的在后续博客有详解

MySQL 系列(一) 生产标准线上环境安装配置案例及棘手问题解决

标签:display   span   文件目录   example   primary   100%   ibdata1   src   prim   

人气教程排行