First, installation
- Yum installation:yum install-y mysql-server MySQL mysql-devel
- Set self-boot:chkconfig mysqld on
- Start MySQL:service mysqld start
- Set root password:/usr/bin/mysqladmin-u root password "123456"
- Installation test:mysql-uroot-p
- Set Allow remote access:
Mysql> Grant all privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with GRANT option;
Mysql> flush Privileges;
- Shut down Port 3306 firewall:
Iptables-a input-p tcp-m TCP--sport 3306-j ACCEPT
Iptables-a output-p tcp-m TCP--dport 3306-j ACCEPT
Service Iptables Save
Service Iptables Restart
Second, commissioning:
- error message: ERROR 1044 (42000): Access denied for user "@ ' localhost ' to database ' MySQL '
error Reason: because the user table of the MySQL database, there is an account named empty username, the anonymous account, resulting in the log in when the use of root, but the actual anonymous login, through the error message "@" localhost can be seen;
Workaround:
1. Turn off MySQL
#Service mysqld Stop
2. Blocking permissions
#Mysqld_safe--skip-grant-table
Screen appears: Starting DEmo from .....
3.Open a new terminal input
# mysql-u Root MySQL
Mysql> Delete from user where user= '; //delete anonymous user (user with blank user name) from user table
Mysql> FLUSH privileges; Make the changes take effect, or if you close the previous terminal, the original error will occur
mysql> quit
- error message: ERROR 1045 (28000): Access denied for user ' root ' @ ' 192.168.0.1 ' (using Password:yes)
Workaround: Configure the MySQL database skip check:
Open /etc/my.cnf, add skip-grant-tablesbelow mysqld , save exit;
Third, other:
Database backup (Backup test database is Test.sql):mysqldump-uroot-p123456 test >test.sql mysqldump--default-character-set=utf8--
routines
--Events
-hlocalhost -uroot-p123456 zclouddb_v2 >/home/tmp/backup.sql (
--routines: Exporting stored procedures and triggers)
Log in to MySQL:mysql-uroot-p
To switch databases:Use test; (if not, first create and then switch, create: Creation database test;)
Database import (Import Test.sql):Source/root/tmp/test.sql
Database deletion:Use MySQL;
DROP DATABASE test;
- Ignore case : Modify/ETC/MY.CNF, add a line under [mysqld]:lower_case_table_names=1
- set maximum number of bytes : Modify/ETC/MY.CNF, add a line under [mysqld]:max_allowed_packet=4m
- Modify Database Password : Use the database connection tool to connect to the database, open the command line interface,
>use MySQL
>update user set Password=password ("New_pass") where user= "root";  
>flush Privileges; 
Restart Mysql:service mysqld restart ;
- Change data storage location:
Shutdown database service: Servicemysqld Stop
Move data file (yum after installation data file location defaults to:/var/lib/mysql):mv/var/lib/mysql/home/data/
Modify the configuration file:vim/etc/my.cnf , the DataDir and socket two lines of content to change to
Datadir=/home/data/mysql and Socket=/home/data/mysql/mysql.sock
Modify the Startup file: vim/etc/init.d/mysqld, change the contents of get_mysql_option mysqld DataDir "/var/lib/mysql" to /home/data/ MySQL
Start Database services: Service mysqld start
MySQL installation and commissioning under CentOS
