Shulou(Shulou.com)06/01 Report--

Theoretical basis of Mysql database 1: compilation and installation 1. Introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database server is fast, reliable and easy to use.

5. MySQL server works in client / server mode, or in embedded system.

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

Second, install MySQL (depending on gcc gcc-c++ ncurses-devel openssl)

2.1. Prepare before compilation and installation

Confirm the system environment

[root@lamp mysql] # uname-a

Linux lamp 2.6.32-71.el6.x86_64 # 1 SMP Wed Sep 1 01:33:01 EDT 2010 x86 "64 GNU/Linux

[root@lamp ~] # ll

-rw-r--r--. 1 root root 5691656 Mar 6 08:54 cmake-2.8.8.tar.gz

-rw-r--r--. 1 root root 24739429 Mar 6 09:04 mysql-5.5.28.tar.gz

[root@lamp ~] # rpm-qa gcc gcc-c++ openssl ncurses-devel

Gcc-4.4.4-13.el6.x86_64

Gcc-c++-4.4.4-13.el6.x86_64

Ncurses-devel-5.7-3.20090208.el6.x86_64

Openssl-1.0.0-4.el6.x86_64

Make sure that the development environment group is installed before installation: Development Tools (development tools) and Development Libraries (development libraries)

[root@lamp ~] # yum groupinstall "Development Tools"

[root@lamp ~] # yum groupinstall "Development Libraries"

* * the data of the mysql database will become larger and larger over time, so the database data should be placed on a separate scalable partition volume for later management and backup, and mounted to the logical volume as follows:

[root@lamp ~] # fdisk / dev/sda # New partition

ACommand (m for help): n

P

Partition number (1-4): 1

Last cylinder, + cylinders or + size {KMagne Mpeng} (1-2610, default 2610): + 10G

Command (m for help): t

Hex code (type L to list codes): 8e

Command (m for help): P

Device Boot Start End Blocks Id System

/ dev/sdb1 1 1306 10490413 + 8e Linux LVM

Command (m for help): W

[root@lamp ~] # partprobe / dev/sda # informs the kernel to reread the sda partition

[root@lamp ~] # fdisk-l # View partitions

Device Boot Start End Blocks Id System

/ dev/sdb1 1 2612 20980858 + 8e Linux LVM

[root@lamp ~] # pvcreate / dev/sda1 # first create a pv physical volume by partitioning / dev/sda1

Physical volume "/ dev/sda1" successfully created

[root@lamp ~] # pvs # View physical volumes

PV VG Fmt Attr PSize PFree

/ dev/sdb1 lvm2 a-10.00g 10.00g

[root@lamp ~] # vgcreate myvg / dev/sda1 # create myvg volume group with / dev/sda1 partition

Volume group "myvg" successfully created

[root@lamp ~] # vgs # View volume groups

VG # PV # LV # SN Attr VSize VFree

Myvg 10 0 wz--n- 10.00g 10.00g

[root@lamp] # lvcreate-n mydata-L 10G myvg # create a size of 10G in the volume group myvg

Logical volume named mydata

Logical volume "mydata" created

[root@lamp ~] # lvs # View logical volumes

LV VG Attr LSize Origin Snap% Move Log Copy% Convert

Mydata myvg-wi-a- 10.00g

[root@lamp ~] # mke2fs-t ext4 / dev/myvg/mydata # format mydata logical volumes

[root@lamp ~] # mkdir / mydata # create a directory as the mount point

[root@lamp ~] # vim / etc/fstab # set logical volumes to boot and mount automatically

# / etc/fstab

Tmpfs / dev/shm tmpfs defaults 0 0

Devpts / dev/pts devpts gid=5,mode=620 0 0

Sysfs / sys sysfs defaults 0 0

Proc / proc proc defaults 0 0

/ dev/myvg/mydata / mydata ext4 defaults 0 0 # add this line

[root@lamp ~] # mount-a # reread / etc/fstab hard disk mount files, making the new partition mount successfully

[root@lamp ~] # mount # View the mounted partitions

...

None on / proc/sys/fs/binfmt_misc type binfmt_misc (rw)

Sunrpc on / var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)

/ dev/mapper/myvg-mydata on / mydata type ext4 (rw) # logical volume mydata has been mounted to the / mydata directory

[root@lamp ~] # mkdir / mydata/data

[root@lamp ~] # ll / mydata

The total dosage is 20

Drwxr-xr-x. 2 root root 4096 February 17 17:30 data

Drwx-. 2 root root 16384 February 17 15:11 lost+found

[root@lamp] # groupadd-r mysql #-r set up a system group

Groupadd: group 'mysql' already exists

[root@lamp] # useradd-r-g mysql-s / sbin/nologin mysql # build a system user cannot log in

Useradd: user 'mysql' already exists

[root@lamp ~] # id mysql # View user information

Uid=27 (mysql) gid=27 (mysql) groups=27 (mysql)

[root@lamp ~] # chown-R mysql.mysql / mydata/data/ # change folder owner and subordinate group-R recursion

[root@lamp ~] # ll / mydata

The total dosage is 20

Drwxr-xr-x. 2 mysql mysql 4096 February 17 17:30 data

Drwx-. 2 root root 16384 February 17 15:11 lost+found

[root@lamp ~] # chmod o-rx / mydata/data/ # remove the read and execute permissions of others in the / data group

[root@lamp ~] # ll / mydata

The total dosage is 20

Drwxr-x---. 2 mysql mysql 4096 February 17 17:30 data

Drwx-. 2 root root 16384 February 17 15:11 lost+found

2.2.Compiler and install cmake-2.8.8.tar.gz:

[root@lamp ~] # tar vxf cmake-2.8.8.tar.gz

[root@lamp ~] # cd cmake-2.8.8

[root@lamp cmake-2.8.8] #. / configure

[root@lamp cmake-2.8.8] # make & & make install

.

-- Installing: / usr/local/doc/cmake-2.8/ccmake.docbook

-- Installing: / usr/local/share/aclocal/cmake.m4 # compilation and installation cmake completed

2.3.Use cmake to compile and install mysql5.5.28:

To clean up the files generated by the previous compilation, use the following command: make clean

[root@lamp ~] # tar vxf mysql-5.5.28.tar.gz

[root@lamp ~] # cd mysql-5.5.28

[root@lamp 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-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci

#-DCMKE_INSTALL_PREFIX= installation path,-DMYSQL_DATADIR= data storage path,-DSYSCONFDIR= configuration file path

-DWITH_INNOBASE_STORAGE_ENGINE=1 (whether to include INNOBASE engine 1 means containing 0 means not)

-DWITH_ARCHIVE_STORAGE_ENGINE=1 (whether to include ARCHIVE engine 1 means containing 0 means not)

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 (whether to include BLACKHOLE hole engine 1 means to include 0 means not to include)

.

-- Performing Test HAVE_PEERCRED-Success

-- Configuring done

-- Generating done

-- Build files have been written to: / root/mysql-5.5.28 # compilation completed

[root@lamp mysql-5.5.28] # make & & make install

.

-- Installing: / usr/local/mysql/man/man1/mysql.1

-- Installing: / usr/local/mysql/man/man1/mysql-test-run.pl.1

-- Installing: / usr/local/mysql/man/man8/mysqld.8 # installation of mysql completed

2.4. configure the mysql script to start and run automatically when it is powered on:

[root@lamp mysql-5.5.28] # chown-R: mysql / usr/local/mysql # change group to mysql

[root@lamp mysql-5.5.28] # cd / usr/local/mysql

[root@lamp mysql] # scripts/mysql_install_db-- user=mysql-- datadir=/mydata/data # initialize mysql and specify user and data directories

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

Support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!

To do so, start the server, then issue the following commands:

. / bin/mysqladmin-u root password' new-password'

. / bin/mysqladmin-u root-h lamp password' new-password'

Alternatively you can run:

. / bin/mysql_secure_installation

Which will also give you the option of removing the test

Databases and anonymous user created by default. This is

Strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

Cd. ;. / bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

Cd. / mysql-test; perl mysql-test-run.pl

Please report any problems with the. / bin/mysqlbug script! # initialization complete

[root@lamp mysql] # mv / etc/my.cnf / etc/my.cnf.back # rename the configuration document in the original system

[root@lamp mysql] # cp support-files/my-large.cnf / etc/my.cnf # copy the configuration document to the / etc directory

[root@lamp mysql] # cp support-files/mysql.server / etc/init.d/mysqld # copy startup script to / etc/init.d directory

[root@lamp mysql] # chkconfig-- add mysqld # add the mysqld startup script to the boot list

[root@lamp mysql] # chkconfig-- list mysqld # check whether the mysqld script starts up or not

Mysqld 0:off1:off2:on3:on4:on5:on6:off

[root@lamp mysql] # service mysqld start # start the mysql script

Starting MySQL... [OK]

[root@lamp mysql] # vim / etc/profile.d/mysql.sh # Edit the environment variable so that the mysql command is in the PATH variable

Export PATH=$PATH:/usr/local/mysql/bin # add this line

[root@lamp mysql] #. / etc/profile.d/mysql.sh # read the contents of the mysql.sh script

[root@lamp mysql] # echo $PATH # output PATH variable content

The path in the / usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin # PATH variable is already in effect

Third, MySQL startup and simple application:

[root@lamp mysql] # mysql # start mysql client program

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.28-log Source distribution

Mysql > SHOW DATABASES; # View basic data

Mysql > SHOW ENGINES; # to view supported engines

| | Engine | Support | Comment | Transactions | XA | Savepoints | |

| | MyISAM | YES | MyISAM storage engine | NO | NO | NO | |

| | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | |

| | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys...

| | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | YES | YES | YES |

| CSV | YES | CSV storage engine | MRG_MYISAM |.

| | ARCHIVE | YES | Archive storage engine | NO | NO | NO | |

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

+-+

8 rows in set (0.00 sec)

Mysql >\ Q # exit mysql

Bye

[root@lamp mysql] # vim / etc/my.cnf # modify mysql configuration document

[client]

# password = your_password

Port = 3306

Socket = / tmp/mysql.sock

# The MySQL server

[mysqld]

Port = 3306

Socket = / tmp/mysql.sock

Skip-external-locking

Key_buffer_size = 256m

Max_allowed_packet = 1m

Table_open_cache = 256

Sort_buffer_size = 1m

Read_buffer_size = 1m

Read_rnd_buffer_size = 4m

Myisam_sort_buffer_size = 64m

Thread_cache_size = 8

Query_cache_size= 16M

# Try number of CPU's*2 for thread_concurrency

Thread_concurrency = 8 # the number of threads needs to be the same as the actual

Datadir = / mydata/data # add this line and specify the mysql data path

[root@lamp mysql] # mysql

Mysql > USE mysql; # set the mysql database as the default database

Database changed

Mysql > SELECT User,Host,Password FROM user; # query the information about User,Host and password in the user table

+-+

| | User | Host | Password | |

+-+

| | root | localhost |

| | root | lamp |

| | root | 127.0.0.1 |

| | root |:: 1 |

| | localhost |

| | lamp |

+-+

6 rows in set (0.00 sec)

Mysql > DROP USER'@ localhost; # Delete the initial anonymous login user

Query OK, 0 rows affected (0.00 sec)

Mysql > DROP USER'@ l # Delete the initial anonymous login user

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT User,Host,Password FROM user; # check again. Anonymous user has been deleted successfully.

+-+

| | User | Host | Password | |

+-+

| | root | localhost |

| | root | lamp |

| | root | 127.0.0.1 |

| | root |:: 1 |

+-+

4 rows in set (0.00 sec)

Mysql > UPDATE user SET Password=PASSWORD ('123456') WHERE user='root'; # set the password for root users

Query OK, 4 rows affected (0.02 sec)

Rows matched: 4 Changed: 4 Warnings: 0

Mysql > SELECT User,Host,Password FROM user; # check again, all root users have set their passwords

+-- +

| | User | Host | Password | |

+-- +

| | root | localhost | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| | root | lamp | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| | root | 127.0.0.1 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| | root |:: 1 | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-- +

4 rows in set (0.00 sec)

Mysql > FLUSH PRIVILEGES; # make the settings take effect immediately

Query OK, 0 rows affected (0.00 sec)

[root@lamp mysql] # mysql # Log in to mysql again with an error because the password has been set.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@lamp mysql] # mysql-uroot-p # Log in to mysql from this computer and enter the password you just set

Enter password: 123456

Mysql > # normal login to mysql

3.2. How to set the local login mysql without entering a password:

Under the home directory, create a new .my.cnf document and add the following:

[root@lamp ~] # vim .my.cnf

[client]

User=root

Host=localhost

Password=123456

# even if root users set a password, they can directly read the hidden files of .my.cnf under the home directory for login authentication without the password.

3.3. Each table is set for a separate space for the innodb engine

[root@lamp ~] # cd / mydata/data/mysql

[root@lamp mysql] # ll

Total 1016

-rw-rw----. 1 mysql mysql 8820 May 11 16:12 columns_priv.frm

-rw-rw----. 1 mysql mysql 0 May 11 16:12 columns_priv.MYD

-rw-rw----. 1 mysql mysql 4096 May 11 16:12 columns_priv.MYI

-rw-rw----. 1 mysql mysql 9582 May 11 16:12 db.frm # MyISAM engine table structure file

-rw-rw----. 1 mysql mysql 880 May 11 16:12 db.MYD # MyISAM engine file for storing data

-rw-rw----. 1 mysql mysql 5120 May 11 16:12 db.MYI # MyISAM engine data index file

-rw-rw----. 1 mysql mysql 10223 May 11 16:12 event.frm

-rw-rw----. 1 mysql mysql 0 May 11 16:12 event.MYD

-rw-rw----. 1 mysql mysql 2048 May 11 16:12 event.MYI

...

Mysql > mysql

Mysql > SHOW VARIABLES LIKE'% innodb%'; # to view related variable parameters about the innodb engine

+-+

| | Variable_name | Value |

+-+

| | have_innodb | YES |

| | ignore_builtin_innodb | OFF |

| | innodb_adaptive_flushing | ON |

| | innodb_adaptive_hash_index | ON |

| | innodb_additional_mem_pool_size | 8388608 | |

| | innodb_autoextend_increment | 8 |

| | innodb_autoinc_lock_mode | 1 | |

| | innodb_buffer_pool_instances | 1 | |

| | innodb_buffer_pool_size | 134217728 | |

| | innodb_change_buffering | all |

| | innodb_checksums | ON |

| | innodb_commit_concurrency | 0 | |

| | innodb_concurrency_tickets | 500 | |

| | innodb_data_file_path | ibdata1:10M:autoextend |

| | innodb_data_home_dir |

| | innodb_doublewrite | ON |

| | innodb_fast_shutdown | 1 | |

| | innodb_file_format | Antelope |

| | innodb_file_format_check | ON |

| | innodb_file_format_max | Antelope |

| | innodb_file_per_table | OFF | # indicates whether the innodb engine enables independent space for each table |

| | innodb_flush_log_at_trx_commit | 1 | |

| | innodb_flush_method |

[root@lamp mysql] # vim / etc/my.cnf # Edit the mysql configuration file and turn on the innodb engine's independent space for each table

# innodb_log_file_size = 64m

# innodb_log_buffer_size = 8m

# innodb_flush_log_at_trx_commit = 1

# innodb_lock_wait_timeout = 50

Innodb_file_per_table = 1 # add this line, 1 to enable, 0 to disable

[root@lamp mysql] # service mysqld restart # restart the mysql service

Shutting down MySQL. [OK]

Starting MySQL.. [OK]

[root@lamp mysql] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

...

Mysql > SHOW VARIABLES LIKE'% innodb%'

+-+

| | Variable_name | Value |

+-+

...

| | innodb_file_format_max | Antelope |

| | innodb_file_per_table | ON | # has been set for innodb engine to have a separate space for each table |

| | innodb_flush_log_at_trx_commit | 1 | |

| | innodb_flush_method |

Mysql > CREATE DATABASE mydb; # create a new database

Query OK, 1 row affected (0.00 sec)

Mysql > USE mydb; # set mydb as the default database

Database changed

Mysql > CREATE TABLE testdb (id INT NOT NULL,name CHAR (30)); # create testdb table 2 rows

Query OK, 0 rows affected (0.03 sec)

Mysql > SHOW DATABASES

+-+

| | Database |

+-+

| | information_schema |

| | mydb |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Tables contained in mysql > SHOW TABLES FROM mydb; # mydb database

+-+

| | Tables_in_mydb |

+-+

| | testdb |

+-+

1 row in set (0.00 sec)

[root@lamp ~] # cd / mydata/data/mydb

[root@lamp mydb] # ll-h

Total 112K

-rw-rw----. 1 mysql mysql 61 Apr 18 15:25 db.opt

-rw-rw----. 1 mysql mysql 8.4K Apr 18 15:27 testdb.frm # test table structure file storage

-rw-rw----. 1 mysql mysql 96K Apr 18 15:27 data and index file for testdb.ibd # test table

[root@lamp ~] # vim test.sql

CREATE DATABASE testdb; # create a testdb database

CREATE TABLE testdb.tb1 (id INT,name CHAR (20)); # create the tb1 table in the testdb library.

Mysql >\. / root/test.sql # Import the contents of test.sql into mysql for execution

Or do not enter the data, just execute: mysql

< /root/test.sql 输入重定向一样。 Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql>

SHOW DATABASES

+-+

| | Database |

+-+

| | information_schema |

| | mydb |

| | mysql |

| | performance_schema |

| | test |

| | testdb |

+-+

6 rows in set (0.00 sec)

Mysql > USE testdb

Database changed

Mysql > SHOW TABLES

+-+

| | Tables_in_testdb |

+-+

| | tb1 |

+-+

1 row in set (0.00 sec)

Mysql > DROP DATABASE testdb; # Delete testdb library

Query OK, 1 row affected (0.01sec)

Attachment 1:

CMake Error atcmake/readlineNaNake:83 appears when compiling mysql

-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)

CMake Error at cmake/readlineNaNake:83 (MESSAGE):

Curses library not found. Please installappropriate package

Remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu,package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.

Call Stack (most recent call first):

Cmake/readlineNaNake:127 (FIND_CURSES)

Cmake/readlineNaNake:217 (MYSQL_USE_BUNDLED_LIBEDIT)

CMakeLists.txt:257 (MYSQL_CHECK_READLINE)

Configuring incomplete, errors occurred!

The reason for this error is that ncurses-devel is not installed, run the following command

Step 1: install

# yum-y install ncurses-devel

Step 2: delete CMakeCache.txt

This is because we are running cmake several times and there is a file we need to delete, delete the CMakeCache.txt file in the current directory and recompile, run the cmake command again and it will work!

[root@mysql mysql-5.6.12] # rm-rf CMakeCache.txt

Or use the find command to find the location of all CMakeCache.txt documents

# find /-name CMakeCache.txt

Then delete all:

# rm-rf/usr/local/src/cmake-2.8.6/Tests/Complex/Cache/CMakeCache.txt

# rm-rf/usr/local/src/cmake-2.8.6/Tests/ComplexOneConfig/Cache/CMakeCache.txt

# rm-rf / usr/local/src/cmake-2.8.6/Tests/ComplexRelativePaths/Cache/CMakeCache.txt

# rm-rf / usr/local/src/mysql-5.5.18/CMakeCache.txt

Delete it all and then re-cmake it to OK.

Annex 2:

Mysql5.5.28.tar.gz download address: http://down.51cto.com/data/700556

Cmake-2.8.8.tar.gz download address: http://vdisk.weibo.com/s/usonnBN1894A3

-end

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.