Shulou(Shulou.com)05/31 Report--

This article mainly explains the "mysql database installation process", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql database installation process" bar!

I. Overview of MySql

The MySql database was developed by the Swedish company MySql AB, which is now owned by Oracle after it was acquired by Oracle. Similar to SQL Server, it is also a database management system based on relational database. MySQL is one of the best RDBMS in Web applications because it belongs to lightweight RDBMS.

Now the latest version of MySql is 5.6.17, the latest download address: http://dev.mysql.com/downloads/mysql/, the download is complete and then you can install and deploy. For more information about installation and deployment, you can check the tutorials online.

II. MySql configuration

Since MySql is based on SQL, then it contains basic DML, DDL, DAL, these basic database languages are easy to use, and MySql also encapsulates a lot of database operation commands, which are run in the dos system, which is different from SQL Server. The environment of MySql is the resume on the dos system, to use the dos command. It is a bit similar to java, it can be said that it is also built on a virtual machine and can be built at once and used everywhere. Some prerequisites are also needed to easily use the MySql command, which is similar to the environment variable of Java. The following method avoids installing the version of MySql as an example to demonstrate its configuration method.

1. MySql environment configuration

You can use the MySql command anywhere by configuring the decompression path of MySql to the system variable.

Note: this is the configured system variable. Any third-party command that uses the console command can be added to the system variable. The system variable is a link, and the system variable is searched first when using the command.

2. MySql server configuration

After configuring the environment variables of the system, you can use all the services provided under MySql's bin, and then you need to install MySQL on the system.

2.1 install MySql server

Open the unzipped files directory, find the suffix .ini file, make a copy of the file renamed my.ini, and replace the original content with the following.

[mysqld]

Basedir=D:/Program Files (x86) / MySql # set the installation directory of mysql

Datadir=D:/Program Files (x86) / MySql/data # sets the data storage directory of the mysql database, which must be data or / / xxx/data

* Segmentation line *

Port = 3306

Socket = / tmp/mysql.sock

Default-character-set=gbk # sets the character set of the mysql server

Skip-locking

Key_buffer = 16K

Max_allowed_packet = 1m

Table_cache = 4

Sort_buffer_size = 64K

Read_buffer_size = 256K

Read_rnd_buffer_size = 256K

Net_buffer_length = 2K

Thread_stack = 64K

[client]

# password = your_password

Port = 3306

Socket = / tmp/mysql.sock

Default-character-set=gbk

* Segmentation line *

Note: [mysqld] the basedir and datadir below need to be set to the path after the file is decompressed. Here, the author puts the file under D:\ Program Files (x86)\ MySql. In addition, the content in the above split line is optional and can be reset when establishing the database. It is recommended not to add it when creating it, because there will be a lot of uncertainties.

The runnable options I configured in the my.ini file are as follows:

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the

# * * default location during install, and will be replaced if you

# * * upgrade to a newer version of MySQL.

[client]

# password = your_password

Port = 3306

Socket = / tmp/mysql.sock

Default-character-set=utf-8

[mysqld]

Port = 3306

Basedir= "C:/Program Files/Mysql"

# set the installation directory of mysql

Datadir= "C:/Program Files/Mysql/data"

# set the storage directory of the data in the mysql database. It must be data or / / xxx/data

Default-storage-engine=INNODB

# default storage engine to be used when creating new tables

Socket = / tmp/mysql.sock

Sql-mode= "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# SQL mode is strict mode

# default-character-set=utf-8

# set the character set of mysql server

# character-set-server=utf-8

# the character set used by the server defaults to the 8-bit coded latin1 character set

Max_connections=100

# maximum number of concurrent connections (number of users) supported by the mysql server. However, one of these connections is always reserved for the administrator to log in with super privileges, even if the maximum number of connections is reached. If the setting is too small and there are more users, "Too many connections" errors often occur.

Query_cache_size=0

# query cache size, which is used to cache SELECT query results. If you have many SELECT queries that return the same query results and rarely change the table, you can set query_cache_size greater than 0, which can greatly improve query efficiency. If the table data changes frequently, do not use this, which will be counterproductive.

# table_cache=256

This parameter, called table_open_cache in versions after 5.1.3, is used to set the number of table caches. In the java tutorial, because each client connection accesses at least one table, the value of this parameter is related to max_connections. When a connection accesses a table, MySQL checks the current number of cached tables. If the table is already open in the cache, the table in the cache is accessed directly to speed up the query; if the table is not cached, the current table is added to the cache and queried. Before performing the caching operation, table_cache is used to limit the maximum number of cached tables: if the currently cached table does not reach table_cache, a new table will be added; if this value has been reached, MySQL will release the previous cache according to the rules such as the last query time and query rate of the cached table.

Tmp_table_size=34M

The maximum size allowed for each temporary table in memory. If the temporary table size exceeds this value, the temporary table is automatically converted to a disk-based table (Disk Based Table).

Thread_cache_size=8

# the maximum number of threads cached. When the client connection is disconnected, if the total number of client connections is less than this value, the thread handling the client task is put back into the cache. In the case of high concurrency, if this value is set too low, many threads will be created frequently, thread creation will become more expensive, and query efficiency will decrease. Generally speaking, if there is good multithreading on the application side, this parameter will not improve the performance much.

#-MyISAM related parameter begin

Myisam_max_sort_file_size=100G

# maximum temporary file size allowed for mysql re-indexing

Myisam_sort_buffer_size=68M

Key_buffer_size=54M

# Key Buffer size, used to cache the index block of the MyISAM table. Determine the speed of database index processing (especially index reading)

Read_buffer_size=64K

# the buffer size used to scan the full table of the MyISAM table. Allocation is made for each thread (provided a full table scan is performed). When sorting a query, MySql scans the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.

Read_rnd_buffer_size=256K

Sort_buffer_size=256K

# connection-level parameters (configured for each thread), 500 threads will consume 500 million 256K of sort_buffer_size.

#-MyISAM related parameter end

#-# InnoDB related parameters begin--

Innodb_additional_mem_pool_size=3M

# InnoDB memory pool size for storing metadata information, which generally does not need to be modified

Innodb_flush_log_at_trx_commit = 1

# transaction-related parameters. If the value is 1, InnoDB will write the transaction log to disk every time commit (disk IO consumption is high), which ensures full ACID feature. If set to 0, it means that the transaction log is written to memory log and the memory log is written to disk once per second. If set to 2, the transaction log is written to memory log at each commit, but the memory log is written to disk once per second.

Innodb_log_buffer_size=2M

# InnoDB log data buffer size. If the buffer is full, the log data in the buffer will be written to disk (flush). Since the disk is usually written at least once a second, there is no need to set it too large, even for long transactions.

Innodb_buffer_pool_size=105M

# InnoDB uses buffer pools to cache index and row data. The higher the value, the less disk IO. This value is generally set to 80% of physical memory.

Innodb_log_file_size=53M

# size of each InnoDB transaction log. Generally set to 25% to 100% of innodb_buffer_pool_size

Innodb_thread_concurrency=9

# maximum number of concurrent threads in InnoDB kernel

#-# InnoDB related parameters end--

Once the my.ini file is configured, you can install the mysqld service in cmd. Run the command in cmd: mysqld-- install MySQL-- defaults-file= "D:\ Program Files (x86)\ MySql\ my.ini", where MySQL is the name of the installation server, and you can specify any name. After the installation is completed, you will be prompted with the following message: Service successfully installed, which means that the installation is successful. After successful installation, the service will be added to the service group policy of the system. You only need to enable it when using it.

Note: when running the installation command, you must pay attention to the path problem in cmd. The path must be in the path where the bin of mysql is located. For example, if my mysql is decompressed to D:\ Program Files (x86)\ MySql folder, then the current path of cmd must be D:\ Program Files (x86)\ MySql\ bin, otherwise an error message: system error 2 will occur when starting the service after installation. The system cannot find the specified file.

2.2 start the server

Start the MySQL server and run the command in cmd: net start MySQL.

2.3 stop the server

After the use is complete, you can stop the server from running by running the command in cmd: net stop MySQL

2.4 View the design server name and password

The default name of the server you just installed is root. There is no password at this time. You can use the cmd command to set the name and password. The corresponding command is: mysql-u root. In addition, you can change the password of root by using the update statement in cmd, as shown in the following code:

1. Add a password to root, ab12.

First enter the directory mysql\ bin under DOS, and then type the following command: mysqladmin-u root-p password ab12.

Note: because root doesn't have a password at the beginning, the-p old password can be omitted.

2. Change the password of root to djg345:mysqladmin-u root-p ab12 password djg345

Delete service: mysqld-- remove MySQL

Use the remove command, followed by the name of the database service you want to delete.

Third, MySql common commands

3.1 connection Services

The two connection methods are local connection and remote connection.

3.1.1 Local connection

Enter and run the command in cmd: mysql-u root-p, and then enter the appropriate password. It is important to note that there can be no space between the user name-u and the user name, that is,-uroot is also correct, but there must be a space between the password and-p. If you have just installed MYSQL, the default root user name does not have a password. Type mysql-u root directly to enter MYSQL. The prompt for MYSQL is: mysql >.

3.1.2 remote connection

Assuming that the IP address of the remote host is 219.243.79.8, the user name is root, and the password is 123, run the following command in cmd: mysql-h319.243.79.8-uroot-p 123.

3.1.3 exit MYSQL command: exit

3.2 add new users

3.2.1 Super user

Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First connect to MYSQL with the root user, and then type the following command:

Grant select,insert,update,delete on *. * to test1@ "%" Identified by "abc"

But the addition of users is very dangerous. If someone knows the password of test1, he can log in to your mysql database on any computer on internet and do whatever he or she wants with your data. See 2 for the solution. 3.2.2 Local users

Add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that even if the user knows the test2 password, he cannot access the database directly from the internet, only through the web page on the MYSQL host.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by ""

3.3The show command

The show command means to view, and can be used to view some list information in MySql, such as: show databases displays the names of all databases; show tables displays all table names in a database.

3.4 operate the database

To enter the relevant database before operation, you can use use commands, such as use testdb to enter the database named testdb. After entering the database, you can operate on the objects in the database, and the corresponding operation commands use SQL statements, DDL, DML, DAL.

3.4.1 View database content

1. View the field information of a table in the database: desc table name

2. View the creation statement of the database table: the name of the show create table table; of course, you can also view other SQL statements that create content using the same method, such as the creation statement of the view database, the name of the show create database database.

3.4.2 modify column types and names in the table

(1) modify only the column type

Alter table database name. Table name modify column column name data type, for example: the sex column of t_animal table should be of boolean type:

Alter table t_animal modify sex boolean not null

(2) modify both column name and column data type alter table table name change column old column name new column name data type, for example: rename sex column of t_animal table to ani_sex, data type to boolean type:

Alter table t_animal change column sex ani_sex boolean not null

Thank you for your reading, the above is the content of "the installation process of mysql database", after the study of this article, I believe you have a deeper understanding of the installation process of mysql database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.