centos6.5 MySQL installation + remote Access + Backup Recovery + basic operations

Reference blog:

Linux Learning CentOS (13) installation and configuration of MySQL database under--centos6.4

MySQL multiple ways to change the root password

Backup and restore of MySQL

Troubleshoot MySQL import restore when the problem is garbled

MySQL Open remote connection method

MySQL grants user permission to the grant command

MySQL database operations common commands

First, installation

1.yum for MySQL installation

I am using yum to perform MySQL database installation, first we can enter Yum List | grep mysql command to view the downloadable version of the MySQL database available on Yum:

[[email protected] ~] # yum list | grep mysql
You can get the downloadable version information of the mysql database on the yum server:

 

Then we can install mysql mysql-server mysql-devel by entering the command yum install -y mysql-server mysql mysql-devel (We need to install the mysql-server server)

[[email protected] ~] # yum install -y mysql-server mysql mysql-devel
1.2 mysql database initialization and related configuration

After we install the mysql database, we will find that there will be an extra mysqld service. This is our database service. We can start our mysql service by entering the service mysqld start command.

Note: If this is the first time we have started the mysql service, the mysql server will first perform the initial configuration, such as:

[[email protected] ~] # service mysqld start
Initialize the MySQL database: WARNING: The host ‘xiaoluo’ could not be looked up with resolveip.
This probably means that your libc libraries are not 100% compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges!
Installing MySQL system tables ...
OK
Filling help tables ...
OK

To start mysqld at boot time you have to copy
support-files / mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!
To do so, start the server, then issue the following commands:

/ usr / bin / mysqladmin -u root password ‘new-password’
/ usr / bin / mysqladmin -u root -h xiaoluo password ‘new-password’

Alternatively you can run:
/ usr / bin / mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd / usr; / usr / bin / mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd / usr / mysql-test; perl mysql-test-run.pl

Please report any problems with the / usr / bin / mysqlbug script! [OK]
Starting mysqld: [OK]
At this time, we will see a lot of information after starting the mysql server for the first time, the purpose is to initialize the mysql database. When we restart the mysql service again, we will not be prompted so much information, such as:

[[email protected] ~] # service mysqld restart
Stop mysqld: [OK]
Starting mysqld: [OK]
 When we use the mysql database, we have to start the mysqld service first. We can use the chkconfig --list | grep mysqld command to check whether the mysql service starts automatically when it is turned on, such as:

[[email protected] ~] # chkconfig --list | grep mysqld
mysqld 0: close 1: close 2: close 3: close 4: close 5: close 6: close
 We found that the mysqld service does not start automatically when it is turned on. Of course, we can set it to start by using the chkconfig mysqld on command, so that we do not have to manually start it each time.

[[email protected] ~] # chkconfig mysqld on
[[email protected] ~] # chkconfig --list | grep mysql
mysqld 0: Disable 1: Disable 2: Enable 3: Enable 4: Enable 5: Enable 6: Disable
 After the mysql database is installed, there will only be a root administrator account, but the root account has not yet been set a password for it. When the mysql service is started for the first time, some initialization of the database will be performed. In the message, we see a line like this:

/ usr / bin / mysqladmin -u root password ‘new-password’ // Set a password for the root account
 So we can use this command to set a password for our root account (note: this root account is the mysql root account, not the Linux root account)

[[email protected] ~] # mysqladmin -u root password ‘root’ // Use this command to set the root account password to root
 At this point we can log in to our mysql database through the mysql -u root -p command

1.3 The main configuration file of the mysql database

1.3.1./etc/my.cnf This is the main configuration file for mysql

We can check some information of this file

[[email protected] etc] # ls my.cnf
my.cnf

[[email protected] etc] # cat my.cnf
[mysqld]
datadir = / var / lib / mysql
socket = / var / lib / mysql / mysql.sock
user = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

[mysqld_safe]
log-error = / var / log / mysqld.log
pid-file = / var / run / mysqld / mysqld.pid
1.3.2./var/lib/mysql Database file storage location of the mysql database

The database files of our mysql database are usually stored in the / ver / lib / mysql directory

[[email protected] ~] # cd / var / lib / mysql /
[[email protected] mysql] # ls -l
Total dosage 20488
-rw-rw ----. 1 mysql mysql 10485760 April 6 22:01 ibdata1
-rw-rw ----. 1 mysql mysql 5242880 April 6 22:01 ib_logfile0
-rw-rw ----. 1 mysql mysql 5242880 April 6 21:59 ib_logfile1
drwx ------. 2 mysql mysql 4096 April 6 21:59 mysql // These two are the two default database files when the mysql database is installed
srwxrwxrwx. 1 mysql mysql 0 April 6 22:01 mysql.sock
drwx ------. 2 mysql mysql 4096 April 6 21:59 test // These two are the default two database files when the mysql database is installed
We can create a database by ourselves to verify the storage location of the database file

Create our own database:
mysql> create database xiaoluo;
Query OK, 1 row affected (0.00 sec)

[[email protected] mysql] # ls -l
Total dosage 20492
-rw-rw ----. 1 mysql mysql 10485760 April 6 22:01 ibdata1
-rw-rw ----. 1 mysql mysql 5242880 April 6 22:01 ib_logfile0
-rw-rw ----. 1 mysql mysql 5242880 April 6 21:59 ib_logfile1
drwx ------. 2 mysql mysql 4096 April 6 21:59 mysql
srwxrwxrwx. 1 mysql mysql 0 April 6 22:01 mysql.sock
drwx ------. 2 mysql mysql 4096 April 6 21:59 test
drwx ------. 2 mysql mysql 4096 April 6 22:15 xiaoluo /// This is the xiaoluo database we just created ourselves
[[email protected] mysql] # cd xiaoluo /
[[email protected] xiaoluo] # ls
db.opt
3./var/log storage location of the mysql database log output

Some log output storage locations of our mysql database are in the / var / log directory

[[email protected] xiaoluo] # cd
[[email protected] ~] # cd / var / log
[[email protected] log] # ls
amanda cron maillog-20130331 spice-vdagent.log
anaconda.ifcfg.log cron-20130331 mcelog spooler
anaconda.log cups messages spooler-20130331
anaconda.program.log dirsrv messages-20130331 sssd
anaconda.storage.log dmesg mysqld.log tallylog
anaconda.syslog dmesg.old ntpstats tomcat6
anaconda.xlog dracut.log piranha wpa_supplicant.log
anaconda.yum.log gdm pm-powersave.log wtmp
audit httpd ppp Xorg.0.log
boot.log ibacm.log prelink Xorg.0.log.old
btmp lastlog sa Xorg.1.log
btmp-20130401 libvirt samba Xorg.2.log
cluster luci secure Xorg.9.log
ConsoleKit maillog secure-20130331 yum.log
The mysqld.log file is where we store some log information generated by our operations with the mysql database. By viewing this log file, we can get a lot of information

 Because our mysql database is accessible through the network, not a stand-alone version of the database, the protocol used is tcp / ip protocol, we all know that the port number bound to the mysql database is 3306, so we can use the netstat -anp command Let's see if the Linux system is listening on port 3306:

The result is shown above. The 3306 port number monitored by the Linux system is our mysql database! !! !! !!

Two ways to change the root password
Method 1: Use the SET PASSWORD command

Mysql -u root

Mysql> SET PASSWORD FOR ‘root’ @ ‘localhost’ = PASSWORD (‘newpass’);

Method 2: Use mysqladmin

Mysqladmin -u root password "newpass"

If root has set a password, use the following method

Mysqladmin -u root password oldpass "newpass"

Method 3: Edit the user table directly with UPDATE

Mysql -u root

Mysql> use mysql;

Mysql> UPDATE user SET Password = PASSWORD (‘newpass’) WHERE user = ‘root’;

Mysql> FLUSH PRIVILEGES;

When the root password is lost, this can be done

Mysqld_safe --skip-grant-tables &

Mysql -u root mysql

Mysql> UPDATE user SET password = PASSWORD ("new password") WHERE user = ‘root’;

Mysql> FLUSH PRIVILEGES;
Third, MySQL backup and restore + Chinese garbled problem
MySQL backup and restore are completed using mysqldump, mysql and source commands.
1.Win32 MySQL backup and restore
1.1 Backup
Start menu | Run | cmd | Use the “cd / Program Files / MySQL / MySQL Server 5.0 / bin” command to enter the bin folder | Use “mysqldump -u username-p databasename> exportfilename” to export the database to a file, such as mysqldump -u root -p voice> voice.sql, then enter the password to start the export.
  
1.2 Restore
Enter the MySQL Command Line Client, enter the password, enter "mysql>", enter the command "show databases;", press Enter, and see what databases there are; create the database you want to restore, enter "create database voice;", press Enter ; Switch to the database just created, enter "use voice;", enter; import data, enter "source voice.sql;", enter, start import, "mysql>" appears again without error, and the restore was successful.
  
2.Backup and restore of MySQL under Linux
2.1 Backup
[[email protected] ~] # cd / var / lib / mysql (Go to the MySQL library directory and adjust the directory according to your MySQL installation)
[[email protected] mysql] # mysqldump -u root -p voice> voice.sql, just enter the password.

2.2 Restore
Method one:
[[email protected] ~] # mysql -u root -p Enter, enter the password, enter the MySQL console "mysql>", and restore with 1.2.

Method two:
[[email protected] ~] # cd / var / lib / mysql (Go to the MySQL library directory and adjust the directory according to your MySQL installation)
[[email protected] mysql] # mysql -u root -p voice <voice.sql, just enter the password.
Chinese garbled characters during restoration:

Solution: 
The first step: the database and tables are set to utf8 format during the restore (utf8 is recommended, of course, gbk or gb2312 is also acceptable)

Copy the code:

CREATE DATABASE `shegongku` DEFAULT CHARACTER SET utf8
CREATE TABLE `members` (
`uid` mediumint (8) unsigned NOT NULL default‘ 0 ’,
`username` varchar (15) NOT NULL default‘ ‘,
`password` varchar (40) NOT NULL default‘ ‘,
`salt` varchar (16) default NULL,
`email` varchar (60) NOT NULL default‘ ‘
) ENGINE = MyISAM DEFAULT CHARSET = utf8;


The second step is to convert the encoding of the sql file into utf8 format, which can be converted to notepad2 or UltraEditor, and then add a set set utf8 to the first sql file.

The third step is to import. Generally, there will be no garbled characters. At this time, if you query again in the terminal cmd and find that the garbled characters are still garbled, this is not a database problem, but a display problem. Enter set names gbk in the terminal, so that the Chinese display is normal.
centos6.5 mysql installation + remote access + backup recovery + basic operations