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

Install MySQL (MySQL-server-5.6.30 for rhel 6.7_64bit) with the full package of rpm

Check whether MySQL is installed on the operating system

[root@mydb1 backup] # rpm-qa | grep mysql

Qt-mysql-4.6.2-26.el6_4.x86_64

Mysql-5.1.71-1.el6.x86_64

Mysql-server-5.1.71-1.el6.x86_64

Mysql-devel-5.1.71-1.el6.x86_64

Mysql-libs-5.1.71-1.el6.x86_64

Delete MyQL (the mysql-libs that comes with the RedHat6.7 server is mysql-libs-5.1.61-4.el6.x86_64, which may conflict with the higher version of mysql-libs)

[root@mydb1 backup] # rpm-qa | grep mysql*

[root@mydb1 backup] # yum-y remove mysql-libs-5.1.73-5.el6_6.x86_64

3. Install MySQL with RPM (installed with root users. There is no need to build mysql groups and mysql users in advance. It will be created automatically during installation of mysql-server)

MySQL-6.30 Yum Repository

Rhel6

Http://repo.mysql.com//mysql57-community-release-el6-8.noarch.rpm

Rhel7

Http://repo.mysql.com//mysql57-community-release-el7-8.noarch.rpm

Official description of MySQL Yum source:

Http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en

# Enable to use MySQL 5.7

[mysql57-community]

Name=MySQL 5.7 Community Server

Baseurl= http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/

Enabled=1

Gpgcheck=1

Gpgkey= file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6

[mysql56-community]

Name=MySQL 5.6 Community Server

Baseurl= http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/

Enabled=1

Gpgcheck=1

Gpgkey= file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Download the full package of rpm and install MySQL

[root@mydb1 backup] # wget http://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-5.6.30-1.el6.x86_64.rpm-bundle.tar

[root@mydb1 backup] # tar-xvf MySQL-5.6.30-1.el6.x86_64.rpm-bundle.tar

[root@mydb1 backup] # rpm-pql MySQL-server-5.6.23-1.el6.x86_64.rpm | more

[root@mydb1 backup] # rpm-ivh MySQL-server-5.6.30-1.el6.i686.rpm

[root@mydb1 backup] # rpm-ivh MySQL-client-5.6.30-1.el6.x86_64.rpm

Or

[root@mydb1 backup] # yum-y install MySQL-server* MySQL-client*

Create an installation directory

[root@mydb1 backup] # mkdir-p / u01/app/mysql/data/mysql3306/ {data,log,iblog,binlog,tmp}

5. Configure my.cnf

[root@wmserpmysql1] # cp-v / usr/my.cnf / u01/app/mysql/data/mysql3306/my.cnf

[root@wmserpmysql1 ~] # vim / u01/app/mysql/data/mysql3306/my.cnf

[client]

Port=3306

Socket=/u01/app/mysql/data/mysql3306/run/mysql.sock

[mysql]

Port=3306

Promprt=\\ u@\\ d\ r:\\ m:\\ s >

[mysqld]

Default-storage-engine=INNODB

Character-set-server=utf8

Explicit_defaults_for_timestamp=true

# dir

Basedir=/usr

Datadir=/u01/app/mysql/data/mysql3306/data

Tmpdir=/u01/app/mysql/data/mysql3306/tmp

Innodb_log_group_home_dir=/u01/app/mysql/log/mysql3306/iblog

Innodb_data_home_dir=/u01/app/mysql/log/mysql3306/iblog

Slave_load_tmpdir=/u01/app/mysql/data/mysql3306/tmp

Log-error=/u01/app/mysql/data/mysql3306/log/alert.log

Slow_query_log_file=/u01/app/mysql/data/mysql3306/log/slow.log

Relay_log_info_file=/u01/app/mysql/log/mysql3306/binlog/relay-log.info

Master-info-file=/u01/app/mysql/log/mysql3306/binlog/master.info

Socket=/u01/app/mysql/data/mysql3306/run/mysql.sock

Log-bin=/u01/app/mysql/log/mysql3306/binlog/binlog

Relay-log=/u01/app/mysql/log/mysql3306/binlog/relaylog

Innodb_force_recovery=0

Execute the MySQL installation script

[root@wmserpmysql1 ~] # sh init3306.sh

Rm-rf / u01amp app _ ql _ hash mysql3306

Rm-rf / u01ActionApp _ Uniql _ _ log _ ql3306 _ iblog _

Rm-rf / u01ActionApp _ qlmax _ log _ ql3306 _ binlog _ _

Chmod-R 777 / u01 * * App * * MySQL *

Chmod-R 777 / u01ActionApplash MySQL _ Log _ ql3306 _ MySQL *

Chmod-R 777 / u01 *

Chmod 755 / u01/app/mysql/data/mysql3306/my.cnf

Mysql_install_db-defaults-file=/u01/app/mysql/data/mysql3306/my.cnf-basedir=/usr/-datadir=/u01/app/mysql/data/mysql3306/data

7. Check whether there is a MySQL service, if no mysql has been added to boot the service.

Chkconfig-add mysqld

Chkconfig-level 2345 mysqld on

Check whether the boot settings are successful

[root@mydb1 ~] # chkconfig-- list | grep mysql

Mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off

8. Start MySQL

[root@mydb1 ~] # service mysql start

Starting MySQL SUCCESS!

9. How to check the running status of MySQL

[root@mydb1 ~] # service mysql status

SUCCESS! MySQL running (4104)

10. Check MySQL initial password

[root@mydb1 ~] # cat / root/.mysql_secret

# The random password set for the root user at Mon Mar 30 15:04:58 2015 (local time): B15xIBTq3xAoGRVx

Log in to MySQL

[root@mydb1] # mysql-u root-p

Enter password:

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

Your MySQL connection id is 6

Server version: 5.6.23

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 >

Change the password

Mysql > show databases

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Mysql > SET PASSWORD = PASSWORD ('guoyJoe123')

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

Remote access using root users:

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' mysql' WITH GRANT OPTION

XIII. My.cnf reference:

MySQL system parameter configuration

Do not start MySQL, copy the data directory to the new location / mysqldata, and modify the permissions

[root@wmserpmysql1 /] # cd / var/lib/mysql/

[root@wmserpmysql1 /] # cp-r * / mysqldata/

[root@wmserpmysql1 /] # chown mysql:mysql-R / mysqldata

Delete the original default parameter file

[root@wmserpmysql1 mysql] # rm / usr/my.cnf

Rm: remove regular file `/ usr/my.cnf'? Y

Re-copy the new parameter file and modify it

[root@wmserpmysql1 mysql] # pwd

/ usr/share/mysql

[root@wmserpmysql1 mysql] # cp my-default.cnf / etc/my.cnf

[root@wmserpmysql1 mysql] # vim / etc/my.cnf

Modify the parameter file / etc/my.cnf

[client]

Port = 3306

Socket = / tmp/mysql.sock

[mysql]

No-auto-rehash

[mysqld]

Port = 3306

Socket = / tmp/mysql.sock

Datadir=/mysqldata

Skip-name-resolve

Character-set-server=utf8

Lower_case_table_names=1

Expire-logs-days=60

Back_log = 500,

Max_connections = 5000

Max_connect_errors = 100000

Table_open_cache = 2048

Max_allowed_packet = 16m

Binlog_cache_size = 1m

Max_heap_table_size = 64m

Sort_buffer_size = 8m

Join_buffer_size = 8m

Thread_cache_size = 8

Thread_concurrency = 16

Query_cache_size = 64m

Query_cache_limit = 2m

Ft_min_word_len = 4

Thread_stack = 19K

Tmp_table_size = 64m

Log-bin=mysql-bin

Binlog_format=mixed

Slow_query_log_file=/mysqldata/localhost-slow.log

Slow_query_log

Long_query_time=1

Relay-log=/mysqldata/relay-bin

Server-id = 1

Key_buffer_size = 32m

Myisam_repair_threads = 1

Myisam_recover

Transaction_isolation = REPEATABLE-READ

Innodb_additional_mem_pool_size = 16m

Innodb_buffer_pool_size = 10G

Innodb_data_file_path = ibdata1:10M:autoextend

Innodb_file_per_table=1

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 16

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 8m

Innodb_log_file_size = 256m

Innodb_log_files_in_group = 6

Innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

Innodb_flush_method=O_DIRECT

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]

Quick

Max_allowed_packet = 16m

[myisamchk]

Key_buffer_size = 512m

Sort_buffer_size = 512m

Read_buffer = 8m

Write_buffer = 8m

[mysqlhotcopy]

Interactive-timeout

[mysqld_safe]

Open-files-limit = 65535

Log-error=/mysqldata/wmserpmysql1.800best.com.err

Pid-file=/mysqldata/wmserpmysql1.800best.com.pid

14. View and set the connection status

The maximum number of connections for MySQL is 100 by default

Client login:

Mysql-uusername-ppassword

Set the new maximum number of connections to 200:

Mysql > set GLOBAL max_connections=200

Displays the currently running Query:

Mysql > show processlist

Show current status:

Mysql > show status

Exit the client:

Mysql > exit

View the current maximum number of connections:

Mysqladmin-uusername-ppassword variables | find "max_con"

Such as:

C:\ mysql\ bin > mysqladmin-uroot-p variables | find "max_con"

Enter password:

| | max_connections | 100 |

| | max_connect_errors | 100 |

15. Query the type of storage engine for MySQL data tables

Question: want to know whether the MySQL database is MyISAM or Innodb. How do I check the type of MySQL database table?

There are two main storage engines used by MySQl: MyISAM and Innodb. MyISAM is non-transactional, so it is faster to read, while InnoDB fully supports fine-grained transaction locking (such as commit/rollback). When creating a new MySQL table, select its type (that is, the storage engine). If there is no choice, the default engine with the preset will be used. If you want to know the type of MySQL data table that already exists, the following methods can be achieved.

Method one

Visit phpMyAdmin to find the default database type from phpMyAdmin. Select the database from phpMyAdmin to view its list of tables. Under the Type column, you will see the data table type for each table.

Method two

You can log in to the MySQL server directly, and another way to identify the storage engine is to log in to the MySQL server and run the following MySQL command:

Mysql > SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME =' my_table'

The above command displays the engine type of the mytable' table in the 'mydatabase' database'.

Method three

Another way to check the engine is to use mysqlshow, a tool that displays database information on the command line. Mysqlshow is available in the MySQL client installation package. To use mysqlshow, you need to provide MySQL server login credentials.

The following command displays specific database information. Under the "Engine" column, you can see the engine used by each table.

$mysqlshow-u-p-I

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.