MySQL5.5 Basic configuration after compilation and installation

Source: Internet
Author: User
Tags mysql client

The software used
Cmake-2.8.8.tar.gz
Mysql-5.5.28.tar.gz

Instructions for compiling and installing MySQL5.5
1. Prepare a piece of LVM disk to store MySQL data for later storage space expansion. (optional)
2. Compiling and installing the CMake environment

Previous versions of MySQL 5.5 can be compiled and installed using make, but later versions of mysql5.5 require CMake to be compiled and installed.
The version of the REDHAT5 series is not installed with the CMake tool, and the CMake tool is installed REDHAT6 the version above

3. Create a MySQL user and group
4. Compile and install MySQL
5. After compiling and installing MySQL, delete the MySQL auto-established user to ensure the security of MySQL

6.mysql5.5 uses the InnoDB storage engine by default, InnoDB stores a tablespace file for all tables;
Recommendation: A separate tablespace file per table; otherwise, a tablespace puts all the tables and index files late management is very cumbersome.


1. Prepare space to store data (optional)

The view disk already has a disk ready for configuring LVM
[Email protected] ~]# fdisk-l
Device Boot Start End Blocks Id System
/DEV/SDB1 1 2610 20964793+ 8e Linux LVM
Creating an LVM Disk
[Email protected] ~]# PVCREATE/DEV/SDB1
Physical volume "/DEV/SDB1" successfully created
[Email protected] ~]# vgcreate MYVG/DEV/SDB1
Volume Group "MYVG" successfully created
[Email protected] ~]# lvcreate-l 10g-n mydata MYVG
Logical volume "MyData" created
[[email protected] ~]# LVS
LV VG Attr lsize Origin snap% Move Log copy% Convert
MyData myvg-wi-a-10.00G
Formatting
[Email protected] ~]# mke2fs-j/dev/myvg/mydata
Set up auto mount on boot
[Email protected] ~]# Vim/etc/fstab
/dev/myvg/mydata/mydata ext3 defaults 0 0
Create a folder and mount it
[Email protected] ~]# Mkdir/mydata
[Email protected] ~]# mount-a

2. Compiling and installing the CMake environment
Compiling and installing cmake-2.8.8
[Email protected] ~]# Tar XF cmake-2.8.8.tar.gz
[[Email protected] ~] #cd cmake-2.8.8
[[email protected] cmake-2.8.8]#./configure && make && make install

3. Create a MySQL user and group
[[email protected] ~]# groupadd-r MySQL
[Ro[email protected] ~]# useradd-r-g mysql-s/sbin/nologin MySQL
Create the directory where the data resides and set the owner and the group as MySQL
[Email protected] ~]# Mkdir/mydata/data
[Email protected] ~]# chown-r mysql.mysql/mydata/data/


4. Compile and install mysqlmysql-5.5.28
[Email protected] ~]# Tar XF mysql-5.5.28.tar.gz
[Email protected] ~]# CD mysql-5.5.28
[Email protected] mysql-5.5.28]# CMake. -LH #生成编译时的头文件库文件 =./configure--help cmake. -lh
[Email protected] 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_zlib=system- Dwith_libwrap=0-dmysql_unix_addr=/tmp/mysql.sock-ddefault_charset=utf8-ddefault_collation=utf8_general_ci
[[Email protected] ~] #make && make install
Change MySQL installation directory to MySQL after compilation is complete
[Email protected] ~]# chown-r: mysql/usr/local/mysql/
Initializing MySQL Database
[Email protected] mysql-5.5.28]# cd/usr/local/mysql/
[Email protected] mysql]# scripts/mysql_install_db--user=mysql--datadir=/mydata/data
Adding sample files and system services
[email protected] mysql]# CP support-files/my-large.cnf/etc/my.cnf
[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld
[Email protected] mysql]# chkconfig--add mysqld
[Email protected] mysql]# chkconfig--list mysqld
Mysqld 0: Off 1: Off 2: Enable 3: Enable 4: Enable 5: Enable 6: Off
Start MySQL
[[Email protected] mysql]# service mysqld start

To be able to use the MySQL client tool directly, you need to edit the PROFILE.D file to add the MySQL client tool to the command path
[Email protected] mysql]# vim/etc/profile.d/mysql.sh
Export path= $PATH:/usr/local/mysql/bin
[Email protected] mysql]#. /etc/profile.d/mysql.sh
[Email protected] mysql]# echo $PATH
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/ Usr/local/mysql/bin

You can then use the client command to connect in.
[[email protected] 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), 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> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

5. Remove the anonymous user that comes with the MySQL installation, and then set the password to the Administrator

View the users of MySQL
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)

Delete two anonymous users and set a password for the administrator
mysql> DROP USER ' @localhost;
Query OK, 0 rows Affected (0.00 sec)

mysql> DROP USER ' @ ' localhost.localdomain ';
Query OK, 0 rows Affected (0.00 sec)

mysql> updata User SET Password=password (' Redhat ') WHERE user= ' root ';
If you do not use the IPv6 address you can also put:: 1 This user delete
Mysql> DROP USER ' root ' @ ':: 1 ';
Query OK, 0 rows Affected (0.00 sec)

The view of anonymous users has been deleted, and Root has been password
Mysql> SELECT User,host,password from User;
+------+-----------------------+-------------------------------------------+
| User | Host | Password |
+------+-----------------------+-------------------------------------------+
| Root | localhost | *84bb5df4823da319bbf86c99624479a198e6eee9 |
| Root | Localhost.localdomain | *84bb5df4823da319bbf86c99624479a198e6eee9 |
| Root | 127.0.0.1 | *84bb5df4823da319bbf86c99624479a198e6eee9 |
+------+-----------------------+-------------------------------------------+
3 Rows in Set (0.00 sec)

Refresh permission-related tables when exiting
mysql> FLUSH privileges;

Quit MySQL is in use MySQL command on connect not come in, want to connect need to specify user name and password
[[email protected] mysql]# MySQL
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)
[Email protected] mysql]# mysql-uroot-p
Enter Password:




6. Storage Engine Settings

Viewing the storage engine
Mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+| engine              | Support | Comment                                                           | transactions | xa   |  savepoints |+--------------------+---------+------------------------------------------------------- ---------+--------------+------+------------+| mrg_myisam          | yes     | collection of identical myisam tables                            | no           | no    | no         | |  InnoDB             | DEFAULT  | supports transactions, row-level locking, and foreign keys      | YES          | YES   | yes        | |  csv                |  YES     | CSV storage engine                                               | no           |  no   | no         | |  BLACKHOLE          | YES      | /dev/null storage engine  (anything you write to it  Disappears)  | NO           | NO    | no         | |  MyISAM             | YES      | MyISAM storage engine                                            | NO           | NO    | no         | |  PERFORMANCE_SCHEMA | YES     | Performance Schema                                                | 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) 

Engine: Names of engines
Support: Whether it is supported, default indicates the storage engine
Comment: A brief description of the engine
Transactions: Whether the transaction is supported
XA: Distributed Transactions
Savepoints: Save Point


MyISAM:
Three files per table:
. frm: Table structure
. MYD: Table Data
. MYI: Table Index

InnoDB:
All tables share a table space file;
Recommendation: A separate tablespace file per table; otherwise, a tablespace puts all the tables and index files late management is very cumbersome.
. frm: Table structure
. IBD: Table space (table data and table index)

Set each table to use a separate tablespace file
[Email protected] mysql]# VIM/ETC/MY.CNF
innodb_file_per_table = 1
[Email protected] mysql]# service mysqld restart
MySQL to see if innodb_file_per_table is enabled
Mysql> SHOW GLOBAL VARIABLES like '%innodb% ';
innodb_file_per_table | On


Create a database and a table
mysql> CREATE DATABASE mydb;
Query OK, 1 row Affected (0.00 sec)

mysql> use MyDB;
Database changed
Mysql> CREATE TABLE testdb (id INT not null,name CHAR (30));
Query OK, 0 rows affected (0.01 sec)
View the table space where the database is stored you will see that MyDB has only one tablespace file
[Email protected] mysql]# Cd/mydata/data
[Email protected] data]# CD mydb/
[[email protected] mydb]# ls
Db.opt testdb.frm TESTDB.IBD

Db.opt defining the default collation and character set for a database


After the MySQL database is installed and initialized, the automatically generated MySQL database uses the MyISAM storage engine, and if you build the table yourself, it depends on the default storage engine that you set up.

View property information for a table
Mysql> SHOW TABLE STATUS like ' user ' \g
1. Row ***************************
Name:user
Engine:myisam
Version:10
Row_format:dynamic
Rows:3
avg_row_length:124
data_length:372
max_data_length:281474976710655
index_length:2048
data_free:0
Auto_increment:null
Create_time:2014-11-16 19:12:16
Update_time:2014-11-16 19:41:14
Check_time:null
Collation:utf8_bin
Checksum:null
Create_options:
Comment:users and Global Privileges
1 row in Set (0.00 sec)

This article from "Plum blossom fragrance from bitter cold" blog, please be sure to keep this source http://wangjunkang.blog.51cto.com/8809812/1581639

MySQL5.5 Basic configuration after compilation and installation

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.