Mysql Database Installation Steps

Mysql Database Installation Steps

Redhat6.5

 

1. Preparations

Uninstall the mysql-server and mysql packages installed using the rpm package
Install the ncurses-devel package
Rpm-ivh/mnt/Packages/ncurses-devel-5.7-3.20090208.el6.x86_64.rpm
① Decompress the cmake package
Tar zxf cmake-2.8.6.tar.gz
[Root @ localhost cmake-2.8.6] # cd cmake-2.8.6
② Install the cmake package
[Root @ localhost cmake-2.8.6] #./configure & gmake install
2. source code compilation and Installation
① Create a running user
[Root @ localhost cmake-2.8.6] # groupadd mysql
[Root @ localhost cmake-2.8.6] # useradd-M-s/sbin/nologin mysql-g mysql
② Decompress the mysql package and release it to the/usr/src directory and switch to the expanded source code directory.
Tar zxf mysql-5.5.22.tar.gz-C/usr/src
Cd/usr/src/mysql-5.5.22/
③ Configuration
Web pages with multiple character sets may be used. The corresponding database system should also support different character set encoding. In the configuration, set the default character set encoding to utf8 and add other character set support.
[Root @ localhost mysql-5.5.22] # cmake-DCMAKE_INSTALL_PREFIX =/usr/local/mysql-DSYSCONFDIR =/etc-DDEFAULT_CHARSET = utf8
-DDEFAULT_COLLATION = utf8_general_ci-DWITH_EXTRA_CHARSETS = all
---- Meaning of the option:
-DCMAKE_INSTALL_PREFIX: Specifies to install the mysql database program to a directory.
-DSYSCONFDIR: Specifies the directory of the initialization parameter file.
-DDEFAULT_CHARSET: Specify the default character set encoding, such as utf8.
-DDEFAULT_COLLATION: Specifies the default character set encoding proofreading rules, utf8_general_ci is a general rule for UTF-8 character set encoding.
-DWITH_EXTRA_CHARSETS: specify additional supported character set encoding.
④ Compile and install make & make install

3. Other adjustments after installation
① Set database Directory Permissions
[Root @ localhost mysql-5.5.22] # chown-R mysql: mysql/usr/local/mysql
② Create a configuration file
The support-file in the mysql source code Directory provides sample configuration files suitable for different load databases. If you are not sure about the database application scale, generally select the my-medium.cnf file, the file can meet the needs of most of the single application Enabled
Create the/etc/my. cnf configuration file of the mysql system according to the following reference content
[Root @ localhost mysql-5.5.22] # rm-rf/etc/my. cnf
[Root @ localhost mysql-5.5.22] # cp support-files/my-medium.cnf/etc/my. cnf
③ Initialize the database
In order to be able to use the mysql database system normally, the initialization supervisor mysql_intall_db should be executed as the running user mysql, and the database storage directory should be specified.
[Root @ localhost mysql-5.5.22] #/usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/ data/
④ Set the variable environment
To use mysql commands in any directory, you need to set environment variables in/etc/profile.
[Root @ localhost mysql-5.5.22] # echo "PATH = $ PATH:/usr/local/mysql/bin">/etc/profile
[Root @ localhost mysql-5.5.22] #./etc/profile or source/etc/profile // take effect immediately
4. Add system services
If you want to add mysql system services for management through chkconfig, you can directly use the service scripts provided in the source code package. Find mysql in the support-files file. Server script file,
Copy it to the/etc/rc. d/init. d directory and change it to mysqld. Then set the execution permission and use the chkconfig command to add it as the mysqld system service.
[Root @ localhost mysql-5.5.22] # cp support-files/mysql. server/etc/rc. d/init. d/mysqld
[Root @ localhost mysql-5.5.22] # chmod + x/etc/rc. d/init. d/mysqld
[Root @ localhost mysql-5.5.22] # chkconfig -- add mysqld
[Root @ localhost mysql-5.5.22] # service mysqld start
View the mysql running status: Port: 3306
[Root @ localhost mysql-5.5.22] # netstat-anpt | grep mysqld
Tcp 0 0 0.0.0.0: 3306 0.0.0.0: * LISTEN 21921/mysqld

2. Access the mysql database
1. log on to the mysql server
During the initialization process after installation, the default username of the mysql database is "root" and the password is blank. If you log on to the database as root, perform the following operations:
Mysql-u root-p
Mysql>
2. Execute the mysql statement: show master logs; to view the log file information accessed by the current database
3. exit mysq and perform quit or exit.

Iii. MySQL operation statement:

1. View
① Check the databases on the current server:
Mysql> show databases;
②. View tables in the currently used database: use the use statement to switch to the used database first, and then view
Mysql> use mysql;
Database changed
Mysql> show tables;
The data files of the MySQL database are stored in the/usr/local/mysql/data directory. Each database corresponds to a sub-directory used to store data table files. A data table corresponds to three file extensions:. frm,. myd, And. myi.
③ View table structure: the DESCRIBE statement is used to display the table structure. You must specify the "database name. Table name"
Mysql> desc mysql. user; // view the structure of the user table of the mysql database

2. Create and delete databases and tables
① Create a new database
Mysql> create database auth; // create an auth Library
Query OK, 1 row affected (0.00 sec)
② Create a new table: You must specify the table name as the parameter and define the fields used by the table.
Mysql> use auth;
Mysql> create table users (user_name char (16) not null, user_passwd char (48) default '', primary key (user_name ));
// Create a users table in the auth database and insert the user_name field (no more than 16 bytes and not empty). user_passwd (no more than 48 bytes) is empty by default, set user_name as the primary key.
Query OK, 0 rows affected (0.02 sec)
Mysql> show tables;
+ ---------------- +
| Tables_in_auth |
+ ---------------- +
| Users |
+ ---------------- +
1 row in set (0.00 sec)
③ Delete tables and databases
Mysql> drop table auth. users; // Delete the users table in the auth Database
Mysql> drop database auth; // Delete the auth database

3. Manage data records in a table
① Insert data records: insert
Mysql> insert into users (user_name, user_passwd) values ('lisi', password ('000000'); // insert a record to the users table in the auth database. The username is lisi, the password is 1234.
Query OK, 1 row affected (0.01 sec)
② Query data records: select
Mysql> select * from users; // query all (*) contents in the users table.
+ ----------- + --------------------------------------------- +
| User_name | user_passwd |
+ ----------- + --------------------------------------------- +
| Lisi | * a4b6157342538724e3560894f7f932c8886ebfcf |
+ ----------- + --------------------------------------------- +
1 row in set (0.00 sec)
③ Modify data record update
Mysql> update auth. users set user_passwd = password ('2014. com ') where user_name = 'lisi ';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
④ Change password:
All user information used to access the mysql database is stored in the user table of the mydql database, where data records can be directly modified.
Mysql> update mysql. user set password = password ('000000') where user = 'root ';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
You can also use the mysqladmin tool in the linux Command Line to set the password. After verifying the original password, you can directly set the Database User Password to '2017. com'
Mysql> mysqladmin-u root-p password '2017. com'
Enter password:
⑤ Delete data records: delete
Mysql> delete from auth. users where user_name = 'lisi ';

4. Authorization: grant
① GRANT permissions: GRANT select ON auth. * TO 'xiaosan' @ 'localhost' identified by '123 ';

② View permission: show grants can use the FOR clause to specify the user objects to be viewed.
Mysql> show grants for 'xiaosan' @ 'localhost ';
+ Shards +
| Grants for xiaosan @ localhost |
+ Shards +
| Grant usage on *. * TO 'xiaosan' @ 'localhost' identified by password' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9' |
| Grant select on 'auth'. * TO 'xiaosan' @ 'localhost' |
+ Shards +
2 rows in set (0.00 sec)
③ Revoke permission: revoke
Revoke all ON auth. * FROM 'xiaosan' @ 'localhost ';

Iv. database backup and recovery
Three formats: (1) Export some tables in the specified database:
Mysqldump [Option] database name [Table Name 1] [Table name 2]...>/backup path/backup file name

(2) Export one or more full databases (including all tables ):
Mysqldump [Option] -- databases database name 1 [database name 2]...>/backup path/backup file name

(3) Back Up All
Mysqldump [Option] -- all-databases>/backup path/backup file name

1. Back up the database
(1) execute the Export Operation
① [Root @ localhost ~] # Mysqldump-u root-p mysql user> mysql-user. SQL // export the user table in the mysql database as a mysql-user. SQL File
Enter password: // Enter the root password

② [Root @ localhost ~] # Mysqldump-u root-p -- databases auth> auth. SQL // export the entire auth database to the auth. SQL File
Enter password: // Enter the root password

③ If you need to back up all databases on the mysql server, use the format (3) when the exported data volume is large, you can add the "-- opt" option to optimize the execution speed.
Example: mysqldump-u root-p -- opt -- all-databases>/all-data. SQL // include all libraries in the created all-data. SQL backup file

(2) view backup content
[Root @ localhost ~] # Grep-v "^ --" auth. SQL | grep-v "^/" | grep-v "^ $" or use egrep-v "^ -- | ^/| ^ $" auth. SQL
Create database /*! 32312 if not exists */'auth '/*! 40100 default character set utf8 */;
USE 'auth ';
Drop table if exists 'users ';
Create table 'users '(
'User _ name' char (16) not null,
'User _ passwd' char (48) DEFAULT '',
Primary key ('user _ name ')
) ENGINE = InnoDB default charset = utf8;
Lock tables 'users' WRITE;
Insert into 'users' VALUES ('lisi', '* AC241830FFDDC8943AB31CBD47D758E79F7953EA ');
Unlock tables;

2. Restore the database
① [Root @ localhost ~] # Mysql-u root-p mysql <mysql-user. SQL // import the user table to the mysql database from the backup file mysql-user. SQL
Enter password:

② [Root @ localhost ~] # Mysql-u root-p <auth. SQL // restore the auth library from the backup file auth. SQL
Enter password: