您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
马哥linux运维学习笔记-mysql系列之三MySQL数据库基础及编译安装(mysql5.5.28)
发布时间：2019-03-13 20:16:10编辑：雪饮阅读（）
编译安装mysql5.5.28前需要先安装cmake
安装cmake2.8.8
[root@localhost src]# tar -zxvf cmake-2.8.8.tar.gz
[root@localhost src]# cd cmake-2.8.8
[root@localhost cmake-2.8.8]# ./configure
[root@localhost cmake-2.8.8]# make && make install
安装mysql5.5.28
[root@localhost cmake-2.8.8]# groupadd -r mysql
[root@localhost cmake-2.8.8]# useradd -r -g mysql -s /sbin/nologin mysql
[root@localhost cmake-2.8.8]# mkdir -p /mydata/data
[root@localhost cmake-2.8.8]# chown -R mysql.mysql /mydata/data/
[root@localhost cmake-2.8.8]# cd /usr/local/src
[root@localhost src]# tar -zxvf mysql-5.5.28.tar.gz
[root@localhost src]# cd mysql-5.5.28
[root@localhost mysql-5.5.28]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock
[root@localhost mysql-5.5.28]#make
[root@localhost mysql-5.5.28]#make install
[root@localhost mysql-5.5.28]# chown -R :mysql /usr/local/mysql/
[root@localhost mysql-5.5.28]# cd /usr/local/mysql/
[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
[root@localhost mysql]# cp support-files/my-large.cnf /etc/my.cnf
在/etc/my.cnf中'thread_concurrency'下面再新增以后配置
'datadir = /mydata/data'
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL.. [ OK ]
[root@localhost mysql]# export PATH=$PATH:/usr/local/mysql/bin
查看mysql数据库所有存储引擎
[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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 engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
mysql>
解决安全隐患
mysql> use mysql
Database changed
删除匿名用户以及不常用用户
mysql> select User,Host,Password from user;
+------+-----------------------+----------+
| User | Host | Password |
+------+-----------------------+----------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
+------+-----------------------+----------+
6 rows in set (0.00 sec)
mysql> drop user '';
Query OK, 0 rows affected (0.01 sec)
mysql> delete from user where User='';
Query OK, 2 rows affected (0.00 sec)
mysql> drop user 'root'@'::1';
Query OK, 0 rows affected (0.00 sec)
mysql> select User,Host,Password from user;
+------+-----------------------+----------+
| User | Host | Password |
+------+-----------------------+----------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
+------+-----------------------+----------+
3 rows in set (0.00 sec)
修改密码
mysql> update user set Password=password('xy220807') where User='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置mysql直连
[root@localhost mysql]# cat ~/.my.cnf
[client]
user='root'
password='xy220807'
host='localhost'
然后直接mysql命令不带任何参数就默认自动通过该配置文件加载密码进行登录了。
[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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>
innodb的表空间
通过上面查看到我们安装的mysql数据库默认的存储引擎是innodb。
现在创建库表如下：
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table tb1(id int not null,name char(30));
Query OK, 0 rows affected (0.02 sec)
然后查看其目录结构
[root@localhost ~]# ls /mydata/data/db1/
db.opt tb1.frm
然后在配置文件' /etc/my.cnf'中添加配置' innodb_file_per_table = 1'并重启mysql
再次创建库表如：
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> use db2;
Database changed
mysql> create table tb1(id int not null,name char(30));
Query OK, 0 rows affected (0.01 sec)
然后也查看其目录结构
[root@localhost ~]# ls /mydata/data/db2/
db.opt tb1.frm tb1.ibd
这样就构成了每个表单独的表空间
上面出现的opt后缀文件是表示该opt文件所在的直接上级目录所对应的数据库在创建时候的默认字符集及其字符集的排序规则。
关键字词：linux,redhat5.8,mysql5.5.28,编译安装,i386