MYSQL introduces the installation and solutions to some problems. mysql introduces some problems.

MYSQL introduces the installation and solutions to some problems. mysql introduces some problems.
I. Introduction

MySQL is the most popular open-source SQL database management system, which is developed, released, and supported by MySQL AB. It has the following features:

• MySQL is a database management system.
• MySQL is an associated database management system.
• MySQL is an open source software.
• The MySQL database server is fast, reliable, and easy to use.
• The MySQL server works in Client/Server mode or in an embedded system.
• There are a large number of available shared MySQL software.

What is the maximum performance of a MySQL table?

The InnoDB Storage engine saves the InnoDB table in one tablespace, which can be created by several files. In this way, the table size can exceed the maximum capacity of individual files. Tablespaces can include original disk partitions, making it possible for large tables. The maximum tablespace capacity is 64 TB.

 

Ii. Install MySQL

Download MySQL address: http://dev.mysql.com/downloads/mysql/

CentOS installation:

yum install mysql-server

Ubuntu installation:

1. sudo apt-get install mysql-server2. sudo apt-get isntall mysql-client3. sudo apt-get install libmysqlclient-dev # Check whether the installation is successful (whether it is in the LISTEN status) sudo netstat-tap | grep mysql

Compile installation MySQL-5.5.32:

# Install the dependency package yum install ncurses-devel gcc-c ++-y # create the directory mkdir-p/home/oldsuo/tools # install the cmake software, gmake compile and install cd/home/oldsuo/tools/tar xf cmake-2.8.8.tar.gzcd cmake-2.8.8. /configure # CMake has bootstrapped. now run gmake. gmakegmake installcd .. /# start installing mysql # create a user and group groupadd mysqluseradd mysql-s/sbin/nologin-M-g mysql # unzip compile MySQLtar zxf mysql-5.5.32.tar.gz cd mysql-5.5.32cmake. -DCMAKE_INSTALL_PREFIX =/appl Ication/mysql-5.5.32 \-DMYSQL_DATADIR =/application/mysql-5.5.32/data \-DMYSQL_UNIX_ADDR =/application/mysql-5.5.32/tmp/mysql. sock \-DDEFAULT_CHARSET = utf8 \-DDEFAULT_COLLATION = utf8_general_ci \-DEXTRA_CHARSETS = gbk, gb2312, utf8, ascii \-DENABLED_LOCAL_INFILE = ON \-DWITH_INNOBASE_STORAGE_ENGINE = 1 \-DWITH_FEDERATED_STORAGE_ENGINE = 1 \-DWITH_BLACKHOLE_STORAGE_ENGINE = 1 \-prop = 1 \-D Records = 1 \-records = 1 \-DWITH_ZLIB = bundled \-DENABLED_LOCAL_INFILE = 1 \-DWITH_READLINE = 1 \-DWITH_EMBEDDED_SERVER = 1 \-DWITH_DEBUG = 0 # -- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32 tips: many options can be configured at the time of compilation, For details, refer to the end of the appendix or official documentation: make # [100%] Built target my_safe_processmake installln-s/application/mysql-5.5.32 // application/mysql if the above operation does not have an error, then MySQL5.5.32 software cmake party Installation is successful. # Copy the configuration file cp mysql-5.5.32/support-files/my-small.cnf/etc/my. cnf # Add a variable and make it take effect echo 'export PATH =/application/mysql/bin: $ PATH '>/etc/profilesource/etc/profileecho $ PATH # authorize the user and/tmp/temporary file directory chown-R mysql. mysql/application/mysql/data/chmod-R 1777/tmp/# initialize the database cd/application/mysql/scripts /. /mysql_install_db -- basedir =/application/mysql/-- datadir =/application/mysql/data/-- user = mysqlcd .. /# Start the database cp support-files/mysql. server/etc/init. d/mysqldchmod + x/etc/init. d/mysqld/etc/init. d/mysqld start # Check the port netstat-lntup | grep 3306

General security operations after compilation and installation:

1. Delete unnecessary users and libraries:

# View User and host columns from mysql. select user, host from mysql. user; # delete a database with an empty username and check the delete from mysql. user where user = ''; select user, host from mysql. user; # Delete the host name as localhost. localdomain library, and check delete from mysql. user where host = 'localhost. localdomain '; select user, host from mysql. user; # Delete the database with the Host Name: 1 and check. : The Role of database 1 is IPV6delete from mysql. user where host = ': 1'; # Delete the test database drop database test;

2. add an additional administrator:

# Add an additional administrator. The system is the administrator and the oldsuo password is mysql> delete from mysql. user; Query OK, 2 rows affected (0.00 sec) mysql> grant all privileges on *. * to system @ 'localhost' identified by 'oldsuo 'with grant option; Query OK, 0 rows affected (0.00 sec) # refresh the MySQL system permission table, make the configuration take effect mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user, host from mysql. user; + -------- + ----------- + | user | host | + -------- + ----------- + | system | localhost | + -------- + ----------- + 1 row in set (0.00 sec) mysql>

3. Set the logon password and start it on startup:

# Set the password and log on to/usr/local/mysql/bin/mysqladmin-u root password 'oldsuo 'mysql-usystem-p # Start mysqld at startup, check chkconfig mysqld onchkconfig -- list mysqld

 

Install and compile mysql5.1.62

 

Iii. Character Set

For new users, Character Set Garbled text is undoubtedly a headache. Small series will leave you with no headache and happiness.

1. Character Set introduction:

Character set, a character set, is a set of symbols that represent characters and the underlying encoding of these symbols. verification rules are a set of rules used to compare characters in the character set. In short, character sets are a set of character symbols and Their encoding and comparison rules. The first computer character set is ASC2. the MySQL database character set includes two concepts: character sets and verification rules, character Set is the storage method that defines the content strings in the database, and the collation is the method that defines the comparison strings.

Suggestion: Select utf8 in both Chinese and English Environments

2. view the set Character Set

# Viewing MySQL character set settings show variables like 'character _ set % '; # viewing the character set of the database show create database db; # view the table's Character set show create table db_tb \ G # query all show collation; # set the table's Character set tables utf8;

View Code3, MySQL DATA garbled and Solutions

1> system aspect cat/etc/sysconfig/i18n LANG = "zh_CN.UTF-8" 2> client (Program), adjusted character set to latin1. Mysql> set names latin1; # Temporarily effective Query OK, 0 rows affected (0.00 sec) # change the parameters of the my. cnf client module to implement the set name latin1 effect and take effect permanently. [Client] default-character-set = latin1 # The service does not need to be restarted. It takes effect after you log out, which is equivalent to set name latin1. 3> server, change my. cnf parameter [mysqld] default-character-set = latin1 # Applicable to 5.1 and earlier versions of character-set-server = latin1 # Applicable to 5.54> database, table, and program # utf8 character set specified for table Creation mysql> create database nick_defailtsss default character set utf8 COLLATE utf8_general_ci; query OK, 1 row affected (0.00 sec)

4. The actual process of modifying the utf8 character set to the GBK character set

1> export table structure # export mysqldump-uroot-p -- default-character-set = utf8-d nick_defailt> alltable in utf8 format. SQL -- default-character-set = gbk # indicates that the GBK character set is connected.-d indicates only the table structure. 2> edit alltable. SQL changes utf8 to gbk. 3> make sure that the database is not updated. Export all data mysqldump-uroot-p -- quick -- no-create-info -- extended-insert -- default-character-set = utf8 nick_defailt> alldata. sql4> open alldata. SQL changes set name utf8 to set names gbk (or modify system server and client) 5> create database oldsuo default charset gbk; 6> create Table and execute alltable. sqlmysql-uroot-p oldsuo <alltable. sql7> import data mysql-uroot-p oldsuo <alltable. SQL

 

Iv. Storage Engine

MySQL is the most commonly used storage engine Myisam and Innodb. After mysql 5.5.5, the default storage engine is Innodb.

Each MySQL engine is used by plug-ins in MySQL. MySQL supports multiple storage engines.

Suggestion: Use the Innodb Engine because rollback is supported.

1. Engine System Files

1) MyISAM engine system database table file [root @ mysql 3306] # ll/data/3306/data/mysql/-rw ----. 1 mysql 10630 October 31 16:05 user. frm # Save the definition of the table-rw ----. 1 mysql 1140 October 31 18:40 user. MYD # data file-rw ----. 1 mysql 2048 October 31 18:40 user. MYI # index file [root @ mysql 3306] # file data/mysql/user. frm data/mysql/user. frm: MySQL table definition file Version 9 [root @ mysql 3306] # file data/mysql/user. MYD data/mysql/user. MYD: DBase 3 data file (167514107 records) [root @ mysql 3306] # file data/mysql/user. MYIdata/mysql/user. MYI: MySQL MISAM compressed data file Version 12) InnoDB Engine [root @ mysql 3306] # ll data/-rw ----. 1 mysql 134217728 October 31 20:05 ibdata1

2. Modify the engine

Alter table student ENGINE = INNODB; alter table student ENGINE = MyISAM;

Modify instance 3. Create a table to specify the engine

mysql> create table mess (                      -> id int(4) not null,                     -> name char(20) not null,    -> age tinyint(2) NOT NULL default '0',    -> dept varchar(16) default NULL    -> ) ENGINE=MyISAM CHARSET=utf8;Query OK, 0 rows affected (0.00 sec)

 

5. Basic statement commands

Running problems:

1. Start a single mysql instance [root @ localhost ~] #/Etc/init. d/mysqld startStarting MySQL [OK] # mysqld_safe-user = mysql & 2. view the MySQL port [root @ localhost ~] # Ss-lntup | grep 3306tcp LISTEN 0 50 *: 3306 *: * users :( ("mysqld", 1965)) 3. view the MySQL process [root @ localhost ~] # Ps-ef | grep mysql | grep-v greproot 19543 1 0 Oct10? 00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe -- datadir =/usr/local/mysql/data -- pid-file =/usr/local/mysql/data/ localhost. localdomain. pidmysql 19651 19543 0 Oct10? 00:05:04/usr/local/mysql/libexec/mysqld -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/data -- user = mysql -- log-error = /usr/local/mysql/data/localhost. localdomain. err -- pid-file =/usr/local/mysql/data/localhost. localdomain. pid -- socket =/tmp/mysql. sock -- port = 33064, MySQL startup principle/etc/init. d/mysqld is a shell STARTUP script. After startup, The mysqld_safe script is called and the mysqld service is called to start mysql. "$ Manager" \ -- mysqld-safe-compatible \ -- user = "$ user" \ -- pid-file = "$ pid_file">/dev/null 2> & 1 & 5 disable database [root @ localhost ~] #/Etc/init. d/mysqld stopShutting down MySQL .... [OK] 6. view the operation command history in the mysql database cat/root /. mysql_history7: Force linux to not record sensitive History commands HISTCONTROL = ignorespace8, mysql to set password/usr/local/mysql/bin/mysqladmin-u root password 'oldsuo '9, mysql to Change password, specify the sock password mysqladmin-uroot-passwd password 'oldsuo '-S/data/3306/mysql for multiple instances. sock

Operation related:

# Log on to the mysql database mysql-uroot-p # view the databases show databases; # Delete the test database drop database test; # use the test database use test; # view the tables show tables; # view all contents of the suoning table select * from suoning; # view the current version select version (); # view the current user select user (); # view the user and host columns from mysql. select user, host from mysql. user; # drop user "" @ localhost; # refresh permission flush privileges; # Run the system ls command out of the database;

 

6. Crack the mysql logon Password

It is also a headache to forget the mysql login password.

1> normal mode #> service mysqld stop #> mysqld_safe -- skip-grant-tables & enter mysql-uroot-p and press enter to enter> use mysql;> update user set password = PASSWORD ("newpass") where user = "root"; Change password to newpassord> flush privileges; update permission> quit service mysqld restartmysql-uroot-p New Password
2> service mysqld stopmysqld_safe -- skip-grant-tables -- user = mysql & mysqlupdate mysql. user set password = PASSWORD ("newpass") where user = "root" and host = 'localhost'; flush privileges; mysqladmin-uroot-pnewpass shutdown/etc/init. d/mysqld startmysql-uroot-pnewpass # login 3> multi-instance killall mysqldmysqld_safe-defaults-file =/data/3306/my. cnf-skip-grant-table & mysql-u root-p-S/data/3306/mysql. sock # specify sock to log on to update mysql. user set password = PASSWORD ("newpass") where user = "root"; flush privileges; mysqladmin-uroot-pnewpass shutdown/etc/init. d/mysqld startmysql-uroot-pnewpass # Login