MySQL's compilation installation and basic configuration

MySQL is an open source single process multi-threaded relational database, which is the C/s architecture, its high reliability, scalability, and ease of use. and support C, C + +, Python, PHP and other development

Versions are available in version a (beta version), version B (Beta), RC Release Candidate, and GA (General availablity) release

Community Edition and Commercial edition, respectively.

I'm here to install the community version of GA version mysql-5.6.34

Installation of MySQL:

1. Dedicated package installation rpm (Rhel SUSE CentoS), Deb

2. Universal binary Format Package gcc:x86, x_64

3. Source code compilation: After MySQL5.5 to compile with CMake

If MySQL and mysqld are on the same UNIX system, interprocess communication is based on Mysql.sock

If MySQL and mysqld are on the same Windows system, interprocess communication is memory-based (shared memory or pipe pipe)

If MySQL and mysqld are not on the same host, interprocess communication is based on TCP/IP and is less efficient than the previous two

MySQL Client tool:

Mysql

Mysqldump

Mysqladmin

Mysqlcheck

Mysqlimport

First, install the CMake

Cross-platform compiler, you can compile a source tree into different versions, such as compiling a 32-bit and a 64-bit and stored in different directories

# Tar XF cmake-2.8.8.tar.gz

# CD cmake-2.8.8

#./configure

# make

# make Install

Second, compile and install mysql-5.5.25a

1. Compile mysql-5.5 with CMake

CMAKE specifies compilation options in a different way from make, which is implemented in the following way:

./configure CMake.

./configure--help CMake. -lh or Ccmake.

Specifies the options that are commonly used when installing the installation path of a file:

-dcmake_install_prefix=/usr/local/mysql

-dmysql_datadir=/data/mysql

-dsysconfdir=/etc

The storage engines that are compiled by default include: CSV, MyISAM, MYISAMMRG, and heap. To install additional storage engines, you can use a compilation option similar to the following:

-dwith_innobase_storage_engine=1

-dwith_archive_storage_engine=1

-dwith_blackhole_storage_engine=1

-dwith_federated_storage_engine=1

To explicitly specify that a storage engine is not compiled, you can use an option similar to the following:

-dwithout_<engine>_storage_engine=1

Like what:

-dwithout_example_storage_engine=1

-dwithout_federated_storage_engine=1

-dwithout_partition_storage_engine=1

If you want to compile into other functions, such as SSL, you can use a library similar to the following, or do not use a library when compiling:

-dwith_readline=1 (can bulk import MySQL data using the load InFile method)

-dwith_ssl=system (SSL enabled)

-dwith_zlib=system (supports ZLIB compression library)

-dwith_libwrap=0 (whether to support tcpwrap implementation control)

Other common options:

-dmysql_tcp_port=3306

-dmysql_unix_addr=/tmp/mysql.sock

-denabled_local_infile=1

-dextra_charsets=all

-ddefault_charset=utf8

-ddefault_collation=utf8_general_ci

-dwith_debug=0

-denable_profiling=1

If you want to clean up the files generated by the previous compilation, you need to use the following command:

Make clean

RM CMakeCache.txt

2, in advance to create an LVM partition, and MySQL user, MySQL group, and the LV format to mount to the/mydata directory, the MySQL initialization when the specified data storage directory is/mydata/data

3. Compile and install

[[email protected] ~]# groupadd-r MySQL #-R Create System user

[Email protected] ~]# useradd-r mysql-g mysql-s/sbin/nologin

[Email protected] ~]# mkdir/mydata/data-p

[Email protected] ~]# chown-r Mysql:mysql/mydata/data # #将存储目录改为mysql用户mysql组

[Email protected] ~]# Cd/tool

[Email protected] tool]# TAR-XF mysql-5.6.34.tar.gz

[Email protected] tool]# CD mysql-5.6.34

[Email protected] mysql-5.6.34]#

CMake \

-dcmake_install_prefix=/usr/local/mysql \

-dmysql_datadir=/usr/local/mysql/data \

-DSYSCONFDIR=/ETC \

-dwith_myisam_storage_engine=1 \

-dwith_innobase_storage_engine=1 \

-dmysql_unix_addr=/var/lib/mysql/mysql.sock \

-dmysql_tcp_port=3306 \

-denabled_local_infile=1 \

-dwith_partition_storage_engine=1 \

-dextra_charsets=all \

-ddefault_charset=utf8 \

-ddefault_collation=utf8_general_ci

[[email protected] mysql-5.6.34]# make && make install

[Email protected] mysql]# chown-r mysql:mysql./*

[Email protected] mysql]# scripts/mysql_install_db--user=mysql--datadir=/mydata/data # #初始化

[Email protected] mysql]# chown-r root/usr/local/mysql/*

[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld #复制样例配置文件

[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld #加入init脚本

[Email protected] mysql]# chkconfig--add MySQL # #加入系统服务

[[Email protected] mysql]# service mysqld start

[Email protected] mysql]# vim/etc/profile.d/mysql.sh

Export path= $PATH:/usr/local/mysql/bin

[Email protected] mysql]#. /etc/profile.d/mysql.sh # #source一下这个文件

[Email protected] mysql]# echo $PATH

See if we can connect it up.

[[email protected] mysql]# MySQL

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 3

Server Version:5.6.34-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

mysql> use MySQL

Mysql> select User,host,password from user;

+------+---------------+-------------------------------------------+

| user | Host | password |

+------+---------------+-------------------------------------------+

| Root |                                           localhost | |

| Root |                                           node1.zxl.com | |

| Root |                                           127.0.0.1 | |

| Root |                                           :: 1 | |

| |                                           node1.zxl.com | |

| Root | 192.168.%.% | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |

+------+---------------+-------------------------------------------+

mysql> drop user ' @localhost; #删除匿名用户

Query OK, 0 rows affected (0.15 sec)

mysql> drop user ' @node1. zxl.com; #删除匿名用户

Query OK, 0 rows Affected (0.00 sec)

mysql> drop user ' root ' @ ':: 1 '; #删除ipv6的用户

Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host,password from user;

+------+---------------+-------------------------------------------+

| user | Host | password |

+------+---------------+-------------------------------------------+

| Root |                                           localhost | |

| Root |                                           node1.zxl.com | |

| Root |                                           127.0.0.1 | |

| Root | 192.168.%.% | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |

+------+---------------+-------------------------------------------+

4 rows in Set (0.00 sec)

#为剩下的所有root用户改密码为 ' 123 '

mysql> Update user set Password=password (' 123 ') where user= ' root ';

Query OK, 4 rows affected (0.03 sec)

Rows Matched:4 Changed:4 warnings:0

Mysql> select User,host,password from user;

+------+---------------+-------------------------------------------+

| user | Host | password |

+------+---------------+-------------------------------------------+

| Root | localhost | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |

| Root | node1.zxl.com | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |

| Root | 127.0.0.1 | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |

| Root | 192.168.%.% | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |

+------+---------------+-------------------------------------------+

4 rows in Set (0.00 sec)

mysql> flush Privileges; #刷新生效

Query OK, 0 rows Affected (0.00 sec)

Mysql> \q

Bye

[[email protected] mysql]# MySQL #退出重新连, not even up

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

[Email protected] mysql]# mysql-uroot-p #指定用户和密码

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 6

Server Version:5.6.34-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>\q

Let MySQL support remote connection

[[Email protected] mysql]# CD

[Email protected] ~]# vim. MY.CNF #在家目录下创建一个隐藏目录

[Client]

User= ' Root '

Password= ' 123 '

host= ' localhost '

[email protected] ~]# MySQL #这样又可以直接连接上去了

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 7

Server Version:5.6.34-log Source Distribution

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| API |

| Archiver |

| Config |

| Data |

| Install |

| MySQL |

| Performance_schema |

| SOURCE |

| static |

| Template |

| Test |

| uc_client |

| Uc_server |

| Ultrax |

+--------------------+

Rows in Set (0.31 sec)

Mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Engine | Support | Comment | Transactions | XA | savepoints |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV Storage Engine | NO | NO | NO |

| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO |

| Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| Performance_schema | YES | Performance Schema | NO | NO | NO |

| ARCHIVE | YES | Archive Storage Engine | NO | NO | NO |

| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |

| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

9 Rows in Set (0.00 sec) # #默认引擎为innodb

Mysql> \q

[Email protected] mysql]# VIM/ETC/MY.CNF

[Mysqld]

Datadir=/mydata/data # #数据存储目录

Innodb_file_per_table = On

Log-bin = Master-bin

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

[Email protected] mysql]# cd/mydata/data/mysql/

[[email protected] mysql]# ls

Structure definition file for db.frm table

Db. Data files for MYD tables

Db. Index file for the MYI table

For the MyISAM engine: There are three files per table. frm (table structure). MYD (table data). MYI (table Index)

For the InnoDB engine: All tables share a table space, but this does not support many advanced features, and it is recommended that you use a separate table space for each table

So how does InnoDB use independent space per table?

Mysql> Show variables like '%innodb% '; #显示关于innodb的所有变量

| innodb_file_per_table | Off |

Mysql> \q

[Email protected] mysql]# VIM/ETC/MY.CNF

innodb_file_per_table = 1

[Email protected] mysql]# service mysqld restart

[[email protected] MySQL] #mysql

Mysql> Show variables like '%innodb% ';

| innodb_file_per_table | On |

Create a database mydb, a table TestDB

Mysql>

mysql> CREATE DATABASE MyDB;

Query OK, 1 row affected (0.43 sec)

Mysql> Use MyDB

Database changed

Mysql> CREATE TABLE TestDB (ID INT not NULL, name char (30));

Query OK, 0 rows affected (0.44 sec)

Mysql> \q

[Email protected] mysql]# cd/mydata/data/mydb/

[[email protected] mydb]# ls

Db.opt #这个数据库的默认排序规则和字符集 (almost every database will be generated)

Testdb.frm #表结构

TESTDB.IBD #每表一个表空间

InnoDB takes one table space per table:. frm (table structure). IBD tablespace (table Data and table index)

Otherwise, all tables use a table space

This article is from the "11097124" blog, please be sure to keep this source http://11107124.blog.51cto.com/11097124/1896888

MySQL's compilation installation and basic configuration