| |
In this article
Create MySQL users and groups
Unpack MySQL source code package
Generate configuration files installed
Compiling and Installing
MySQL configuration file
Create the MySQL grant tables
MySQL directory authorization
Start MySQL
Verify MySQL installation
Setting MySQL Access
MySQL at startup
Setting environment variables
Reference material
A recent project, I used Oracle before, if in a Windows environment, whether it is installed or configured, are easy; have not tried Linux environment, the company has just graduated, is the Linux environment. Usually wait until the development of the time, according to the database over early, development fail to get this live dry. Now, quit after the company encountered two problems with MySQL ~:
1, the performance difference for a table that contains a binary field, development and test environments is huge, is a virtual machine. Development environment, Windows platform, execute INSERT quickly, a few milliseconds things; and the test environment, Linux platform, execute INSERT slow to be justified, tens of milliseconds, the gap is nearly 30 times. If Oracle, pre words, even if do not do anything, quickly;
Online Some people tell me, you should check the machine load, for example, disk, IO and so on, I'd like to, nor is it a production environment, the difference is so big it will not, and has no faster than the ~
2, the product environment, 70W data, execute SELECT * FROM T1 WHERE FLAG = 0 ORDER BY ID ASC, if the FLAG field is not indexed, the execution time of almost one second it. If this was on Oracle absolutely impossible ~
This prompted me to install their own MySQL source code on Linux. When right, exercise, look ~
Benefits with source code installed is compiled, it is possible for their hardware environment. Just install the RPM compared to trouble spots, but "who will be difficult, difficult person would not," and thought more like a ~
Period, beginning with the RPM package, very easy, try the Community Edition Percona and, finally, with the source code and installed it again ~
This article is based on MySQL source code installations. Numerous reference materials, toss several days, finally the placement of the ~ If you install, the lack of the necessary package, use yum to install, such as cmake, perl, etc. ~
Create MySQL users and groups
The purpose of this step is to execute mysql_install_db to create MySQL grant tables and start MySQL, need to specify a user name. Therefore, it is the first step.
The first step: for the future of MySQL service mysqld add users and groups a logged in user and group names are "mysql". If you use a different name in the back of the operation should be replaced.
[Root @ linuxidc usr] # groupadd mysql
[Root @ linuxidc usr] # useradd -g mysql mysql
Unpack MySQL source code package
Suppose you put the MySQL source code package placed in the / usr / local / src directory.
Step two: Unzip mysql packages.
[Root @ linuxidc /] # cd / usr / local / src
[Root @ linuxidc src] # ls
mysql-5.6.28.tar.gz
[Root @ linuxidc src] # tar zxf mysql-5.6.28.tar.gz
[Root @ linuxidc src] # ls
mysql-5.6.28 mysql-5.6.28.tar.gz
[Root @ linuxidc src] #
In this case, you'll see a new directory mysql-5.6.28.
Wherein, when extracting, -x to unpack; -z for the package have gzip property; -f to use the file name. Of course, you can also use -v display decompression process.
Generate configuration files installed
Choose one of the following two steps, MySQL earlier, provide Configure before compiling files for installation and configuration; but do not provide the high version of the file, instead of using cmake.
Suppose, MySQL installed to / usr / local / mysql.
third step:
Use "Configure" command system configuration required for the installation environment and installation configuration file is generated
[Root @ linuxidc mysql-5.6.28] # ./configure
> --prefix = / Usr / local / mysql
> --with-Extra-charsets = all
If cmake skip this step.
Use "cmake" system configuration required for the installation environment and installation configuration file is generated
[Root @ linuxidc mysql-5.6.28] # cmake
> -DDEFAULT_CHARSET = Utf8
> -DDEFAULT_COLLATION = Utf8_general_ci
> -DCMAKE_INSTALL_PREFIX = / Usr / local / mysql
> -DEXTRA_CHARSETS = All
MySQL installation location designated as "/ usr / local / mysql", and the use of the default character set and so on.
When I test, if you do not configure the character set of related options in the implementation of that step back mysql_install_db Times various character sets error.
cmake specific parameters, see MySQL Source-Configuration Options
Compiling and Installing
Compilation and installation is very simple.
Step four: Use the "make" command to compile the source code files, and generate the installation files, and then "make install" command to install.
[Root @ linuxidc mysql-5.6.28] # make && make install
Or compile and install separate execution:
[Root @ linuxidc mysql-5.6.28] # make
[Root @ linuxidc mysql-5.6.28] # make install
Since then, MySQL will be installed to / usr / local / mysql.
MySQL configuration file
Step 5: Create MySQL database server configuration file, you can use the source code package support-files directory my-default.cnf file as a template, copy it to the / etc / directory, rename it to "my.cnf" file.
[Root @ linuxidc /] # cd /usr/local/src/mysql-5.6.28
[Root @ linuxidc mysql-5.6.28] # cp support-files / my-default.cnf /etc/my.cnf
With vi /etc/my.cnf modify the configuration file, as follows.
[Mysqld]
basedir = / usr / local / mysql
datadir = / usr / local / mysql / data
sock = /tmp/mysql.sock
Template file does not only have the source code, the installation directory, too.
Create the MySQL grant tables
Step Six: If you have not installed MySQL, you must create the MySQL grant tables. Enter the installation directory (/ usr / local / mysql), execute the mysql_install_db script bin directory, MySQL database initialization authorization form, which allows access to the storage server.
When MySQL fails or needs to add a new mysql instance, you need to initialize the mysql database. Use --help you can view the supported options.
[Root @ linuxidc mysql] # cd / usr / local / mysql
root @ linuxidc mysql] # scripts / mysql_install_db --user = mysql
Installing MySQL system tables ... 2016-02-02 02:42:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-02 02:42:33 0 [Note] ./bin/mysqld (mysqld 5.6.28) starting as process 28034 ...
2016-02-02 02:42:33 28034 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-02 02:42:33 28034 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-02 02:42:33 28034 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-02-02 02:42:33 28034 [Note] InnoDB: Memory barrier is not used
2016-02-02 02:42:33 28034 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-02 02:42:33 28034 [Note] InnoDB: Using Linux native AIO
2016-02-02 02:42:33 28034 [Note] InnoDB: Using CPU crc32 instructions
2016-02-02 02:42:34 28034 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-02-02 02:42:34 28034 [Note] InnoDB: Completed initialization of buffer pool
2016-02-02 02:42:34 28034 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
......
If a root user run the above command, you should use the --user option, the value of the option and you should be the first step in the logon account created for running the server (in this case is the mysql user) the same. If mysql user logs on, run the above command, you can omit the --user option.
After you create the MySQL grant tables with mysql_install_db, you need to manually restart the server.
If you perform this step when the error, indicating a problem with your third step.
MySQL directory authorization
Step 7: MySQL installation directory bin directory under the ownership of the file to execute root user, change the ownership of the data directory of the user running mysqld mysql program. If you are in the installation directory (/ usr / local / mysql), the command line is as follows.
[Root @ linuxidc mysql-5.6.28] # cd / usr / local / mysql
[Root @ linuxidc mysql] # pwd
/ Usr / local / mysql
[Root @ linuxidc mysql] # chown -R root.
[Root @ linuxidc mysql] # chown -R mysql data
[Root @ linuxidc mysql] # chgrp -R mysql.
[Root @ linuxidc mysql] # ls -l
total 180
drwxr-xr-x 2 root mysql 4096 Feb 2 02:23 bin
-rw-r - r-- 1 root mysql 17987 Nov 16 17:38 COPYING
drwxr-xr-x 5 mysql mysql 4096 Feb 2 09:20 data
drwxr-xr-x 2 root mysql 4096 Feb 2 02:23 docs
drwxr-xr-x 3 root mysql 4096 Feb 2 02:23 include
-rw-r - r-- 1 root mysql 105684 Nov 16 18:45 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 Feb 2 02:23 lib
drwxr-xr-x 4 root mysql 4096 Feb 1 08:45 man
-rw-r - r-- 1 root mysql 943 Feb 1 08:46 my.cnf
-rw-r - r-- 1 root mysql 943 Feb 2 02:42 my-new.cnf
drwxr-xr-x 10 root mysql 4096 Feb 1 08:45 mysql-test
-rw-r - r-- 1 root mysql 2496 Nov 16 17:38 README
drwxr-xr-x 2 root mysql 4096 Feb 1 08:45 scripts
drwxr-xr-x 28 root mysql 4096 Feb 1 08:45 share
drwxr-xr-x 4 root mysql 4096 Feb 1 08:45 sql-bench
drwxr-xr-x 2 root mysql 4096 Feb 1 08:45 support-files
[Root @ linuxidc mysql] #
Note change in the third column. In addition,
"Chown -R root.", All the attributes of the file to the root user. Note that point, all of the files;
"Chown -R mysql data", all the attributes of the data directory to mysql users;
"Chgrp -R mysql.", The group attribute to mysql group. Note that point, all of the files.
Start MySQL
So far, all the necessary things are installed, you can start the MySQL service.
Step eight: Start MySQL. Of course, you can let automatically run MySQL boot described later.
[Root @ linuxidc ~] # cd / usr / local / mysql
[Root @ linuxidc mysql] # bin / mysqld_safe --user = mysql
[Root @ linuxidc mysql] #
Verify MySQL installation
Next, verify, MySQL service is normal.
Step 9: After the MySQL database service is started, look at its port 3306 is open, if you see the following results show that the MySQL service is started successfully.
[Root @ linuxidc mysql] # netstat -tnl | grep 3306
tcp 0 0 ::: 3306 ::: * LIST EN
[Root @ linuxidc mysql] #
Step 10: Use mysqladmin to verify that the server is running. The following commands provide a simple test to check whether the server is up and can respond to connection.
[Root @ linuxidc mysql] # pwd
/ Usr / local / mysql
[Root @ linuxidc mysql] # bin / mysqladmin version
bin / mysqladmin Ver 8.42 Distrib 5.6.28, for Linux on x86_64
Copyright (c) 2000, 2015, 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.
Server version 5.6.28
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 9 min 7 sec
Threads: 1 Questions: 9 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.016
[Root @ linuxidc mysql] #
[Root @ linuxidc mysql] # bin / mysqladmin variables
+ ------------------------------------------------- ------- + ------------------------------------------ ---------------------------------------- +
| Variable_name | Value |
+ ------------------------------------------------- ------- + ------------------------------------------ ---------------------------------------- +
| Auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
| Autocommit | ON |
| Automatic_sp_privileges | ON |
| Avoid_temporal_upgrade | OFF |
| Back_log | 80 |
| Basedir | / usr / local / mysql |
...... |
+ ------------------------------------------------- ------- + ------------------------------------------ ---------------------------------------- +
[Root @ linuxidc mysql] #
Setting MySQL Access
Tenth step: Set the access permissions.
Mysql_install_db Installing MySQL database using the grant tables define the initial MySQL user accounts and access rights, all accounts have no password. These account for the super user, you can do anything.
The initial root account password is empty, anyone can not enter any password with root account can connect to the MySQL server, and has all the rights, which means MySQL installation unprotected. Therefore, you should specify a password for the anonymous accounts or anonymous accounts deleted, specify a password for the MySQL root account. Use "mysql -u root" start the MySQL client console, connect to the MySQL server. Command line is as follows.
[Root @ linuxidc mysql] # cd / usr / local / mysql
[Root @ linuxidc mysql] # bin / mysql -u root
Welcome to the MySQL monitor Commands end with;. Or g.
Your MySQL connection id is 7
Server version: 5.6.28 Source distribution
Copyright (c) 2000, 2015, 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> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 2016-02-02 08:02:31 |
+ --------------------- +
1 row in set (0.00 sec)
mysql>
Step Twelve: If anonymous accounts exist, and have full access, therefore, it can be deleted to improve safety, the MySQL client, run the following command.
mysql> select * from mysql.user G
*************************** 1. row ******************** *******
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
*************************** 2. row ******************** *******
Host: linuxidc
User: root
Password:
Select_priv: Y
......: ......
*************************** 3. row ******************** *******
Host: 127.0.0.1
User: root
Password:
Select_priv: Y
......: ......
*************************** 4. row ******************** *******
Host: :: 1
User: root
Password:
Select_priv: Y
......: ......
*************************** 5. row ******************** *******
Host: localhost
User:
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: NULL
password_expired: N
*************************** 6. row ******************** *******
Host: linuxidc
User:
Password:
Select_priv: N
......: ......
6 rows in set (0.00 sec)
mysql>
mysql> DELETE FROM mysql.user WHERE Host = 'localhost' and User = '';
Query OK, 1 rows affected (0.08 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 rows affected (0.08 sec)
Step 13: You can specify a password for the root accounts in several ways. One of them, use SET PASSWORD specify a password on the command line MySQL client, be sure to use the PASSWORD () function to encrypt the password. For example, the following set localhost domain password is "123456." Other fields can use the same statement, SQL statement is as follows.
mysql> SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD ( '123456');
Query OK, 0 rows affected (0.02 sec)
mysql>
Step Fourteen: If you want to exit the MySQL client, you can enter exit or quit, you can also use the key combination Ctrl + C. Because the root account password has been set, it is necessary to re-log in the MySQL client provides a password to enter.
mysql> exit
[Root @ linuxidc mysql] #
[Root @ linuxidc mysql] # bin / mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor Commands end with;. Or g.
Your MySQL connection id is 13
Server version: 5.6.28 Source distribution
Copyright (c) 2000, 2015, 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>
If you want to shut down the MySQL server, MySQL server command line using the mysqladmin command given by the database administrator user root -u option, -p option gives the password to shut down the MySQL server.
[Root @ linuxidc mysql] # bin / mysqladmin -u root -p shutdown
Enter password:
[Root @ linuxidc mysql] #
MySQL at startup
Step 15: it is necessary to MySQL service set to start automatically. The method is to enter the MySQL source code directory (/usr/local/src/mysql-5.6.28), copy the mysql.server file subdirectory under the support-files directory to /etc/rc.d/init.d/ in both command mysqld.
[Root @ linuxidc mysql] # cd /usr/local/src/mysql-5.6.28
[Root @ linuxidc mysql-5.6.28] # pwd
/usr/local/src/mysql-5.6.28
[Root @ linuxidc mysql-5.6.28] # cp support-files / mysql.server /etc/rc.d/init.d/mysqld
[Root @ linuxidc mysql-5.6.28] #
At this point, you would be able to manually start / stop MySQL database. Command is as follows:
[Root @ linuxidc mysql] # service mysqld status
SUCCESS! MySQL running (24426)
[Root @ linuxidc mysql] # service mysqld stop
Shutting down MySQL..141223 11:37:29 mysqld_safe mysqld from pid file /mysql/data/testdb1.pid ended
SUCCESS!
[1] + Done bin / mysqld_safe --user = mysql (wd: / mysql)
(Wd now: / etc)
[Root @ linuxidc mysql] # service mysqld start
Starting MySQL. SUCCESS!
[Root @ linuxidc mysql] #
However, if you want MySQL to start automatically at boot time, you also need the following settings.
/etc/rc.d/init.d/mysqld Modify permissions, as follows:
[Root @ linuxidc mysql-5.6.28] # chown root.root /etc/rc.d/init.d/mysqld
[Root @ linuxidc mysql-5.6.28] # chmod 755 /etc/rc.d/init.d/mysqld
[Root @ linuxidc mysql-5.6.28] #
Use chkconfig command to set the system to run at different levels since the start of the policy, the first to use "chkconfig --add mysqld" command to increase the designated mysqld services to chkconfig command to manage it, and at the same time increasing relevant in the system startup file description data. Use the command as follows:
[Root @ linuxidc mysql-5.6.28] # chkconfig --add mysqld
[Root @ linuxidc mysql-5.6.28] #
Then, use "chkconfig --level 3 mysqld on" command and the "chkconfig --level 5 mysqld on" command, open the mysqld service in the third grade and fifth grade, that automatically open when mysqld character mode and graphics mode is activated service. Command is as follows:
[Root @ linuxidc mysql-5.6.28] # chkconfig --level 3 mysqld on
[Root @ linuxidc mysql-5.6.28] # chkconfig --level 5 mysqld on
[Root @ linuxidc mysql-5.6.28] #
Then use "chkconfig --list" command to check the settings. Command is as follows:
[Root @ linuxidc mysql-5.6.28] # chkconfig --list mysqld
mysqld 0: off 1: off 2: on 3: on 4: on 5: on 6: off
[Root @ linuxidc mysql-5.6.28] #
Since then, MySQL installation configuration end.
Setting environment variables
After you set the environment variable, you can not enter the full path to the MySQL client command anyway.
Edit etc / my.cnf, and add the following contents:
[Mysqld]
lower_case_table_names = 1
Set the environment:
[Root @ linuxidc mysql] # cd / etc
[Root @ linuxidc etc] # vi profile
The last increase in the file:
PATH = / opt / mysql / bin: / opt / mysql / lib: $ PATH
export PATH
Entry into force of environment variables:
[Root @ linuxidc etc] # source profile |
|
|
|