Install and configure the Mysql database under centos, import the database, authorize a remote ip address, and centos Mysql
Uninstall the original mysql
Because mysql databases are too popular on Linux, the mainstream Linux system versions currently downloaded are basically integrated with mysql databases, run the following command to check whether the mysql database has been installed on the operating system:
[Root @ xiaoluo ~] # Rpm-qa | grep mysql // This command will check whether the mysql database has been installed on the Operating System
If yes, run the rpm-e command or the rpm-e -- nodeps command to uninstall it.
[Root @ xiaoluo ~] # Rpm-e mysql // normal deletion mode [root @ xiaoluo ~] # Rpm-e -- nodeps mysql // strong deletion mode. If the above command is used to delete other dependent files, you can use this command to forcibly delete them.
After deletion, run the rpm-qa | grep mysql command to check whether mysql has been uninstalled successfully !!
Install mysql using yum
I am using yum to install mysql databases. First, we can enter the yum list | grep mysql command to view the downloadable mysql database version on yum:
[root@xiaoluo ~]# yum list | grep mysql
Then, run the yum install-y mysql-server mysql-devel command to install mysql-server mysql-devel (Note: When installing mysql, We have installed the mysql database instead of the mysql client. We also need to install the mysql-server.)
Note: mysql-deve is used for C Language Development interfaces and can be installed without installation.
[root@xiaoluo ~]# yum install -y mysql-server mysql mysql-deve
Mysql database initialization and related configurationAfter installing the mysql database, we will find an additional mysqld service. This is our database service. We can start our mysql service by entering the service mysqld start command.
Note:: If we start the mysql service for the first time, the mysql server will first perform Initialization Configuration, for example:
[root@xiaoluo ~]# service mysqld start[root@xiaoluo ~]# service mysqld restart
When using the mysql database, we have to start the mysqld service first. We can run the chkconfig -- list | grep mysqld command to check whether the mysql service is automatically started upon startup, for example:
[Root @ xiaoluo ~] # Chkconfig -- list | grep mysqldmysqld 0: Close 1: Close 2: Close 3: Close 4: Close 5: Close 6: Close
We found that the mysqld service is not automatically started when it is started. Of course, you can use the chkconfig mysqld on command to set it to boot, so that you do not need to start it manually every time.
[Root @ xiaoluo ~] # Chkconfig mysqld on [root @ xiaoluo ~] # Chkconfig -- list | grep mysqlmysqld 0: Disable 1: Disable 2: Enable 3: Enable 4: Enable 5: Enable 6: Disable
After the mysql database is installed, there will be only one root administrator account, but the root account has not set a password for it. When the mysql service is started for the first time, some database initialization will be performed, in a large string of output information, we can see such a line of information:
/Usr/bin/mysqladmin-u root password 'new-password' // set a password for the root account
So we can use this command to set the password for our root Account (Note:: This root account is the root account of mysql, not the root account of Linux)
[Root @ xiaoluo ~] #Mysqladmin-u root password'Root' // Use this command to set the password of the root account to root.
Now we can log on to our mysql database using the mysql-u root-p command.
V. Main configuration files of the mysql database
1./Etc/my. cnfThis is the main configuration file of mysql.
We can check some information about this file.
[root@xiaoluo etc]# ls my.cnf my.cnf[root@xiaoluo etc]# cat my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
2./Var/lib/mysqlDatabase file storage location of the mysql database
The database files of our mysql database are usually stored in the/ver/lib/mysql directory.
[Root @ xiaoluo ~] # Cd/var/lib/mysql/[root @ xiaoluo mysql] # ls-l total usage 20488-rw-rw ----. 1 mysql 10485760 April 6 22:01 ibdata1-rw-rw ----. 1 mysql 5242880 April 6 22:01 ib_logfile0-rw-rw ----. 1 mysql 5242880 April 6 21:59 ib_logfile1drwx ------. 2 mysql 4096 21:59 mysql // these two are the default two database files srwxrwxrwx during mysql database installation. 1 mysql 0 April 6 22:01 mysql. sockdrwx ------. 2 mysql 4096 April 6 21:59 test // these two are the default two database files during mysql Database Installation
We can create a database to verify the storage location of the database file.
Create a database of our own: mysql> create database xiaoluo; Query OK, 1 row affected (0.00 sec) [root @ xiaoluo mysql] # ls-l total usage 202.16-rw-rw ----. 1 mysql 10485760 April 6 22:01 ibdata1-rw-rw ----. 1 mysql 5242880 April 6 22:01 ib_logfile0-rw-rw ----. 1 mysql 5242880 April 6 21:59 ib_logfile1drwx ------. 2 mysql 4096 April 6 21:59 mysqlsrwxrwxrwx. 1 mysql 0 April 6 22:01 mysql. sockdrwx ------. 2 mysql 4096 April 6 21:59 testdrwx ------. 2 mysql 4096 22:15 xiaoluo // This is the xiaoluo database we just created [root @ xiaoluo mysql] # cd xiaoluo/[root @ xiaoluo] # lsdb. opt
3./Var/logLog output location of the mysql database
Some log output locations of our mysql database are stored in the/var/log directory.
[root@xiaoluo xiaoluo]# cd [root@xiaoluo ~]# cd /var/log[root@xiaoluo log]# lsamanda cron maillog-20130331 spice-vdagent.loganaconda.ifcfg.log cron-20130331 mcelog spooleranaconda.log cups messages spooler-20130331anaconda.program.log dirsrv messages-20130331 sssdanaconda.storage.log dmesg mysqld.log tallyloganaconda.syslog dmesg.old ntpstats tomcat6anaconda.xlog dracut.log piranha wpa_supplicant.loganaconda.yum.log gdm pm-powersave.log wtmpaudit httpd ppp Xorg.0.logboot.log ibacm.log prelink Xorg.0.log.oldbtmp lastlog sa Xorg.1.logbtmp-20130401 libvirt samba Xorg.2.logcluster luci secure Xorg.9.logConsoleKit maillog secure-20130331 yum.log
Import database:
Log on to mysql-p and run source/xx. SQL.
Remote ip authorized login:
Mysql> grant all privileges on *. * to 'root' @ '2017. 141.190.16 'identified by 'hello54987663'
->;
Query OK, 0 rows affected (0.02 sec)
Mysql> flush privileges
->;
Query OK, 0 rows affected (0.00 sec)
How to connect to a remote mysql database locally and then import the SQL file without using the phpadmin method,
Download a MYSQL management tool with an interface. Use the remote connection in it,
Use commands such as navicat and source: path + file name,
Or create a new query, copy the SQL content, and run it.
Mysql does not support remote connection by default. grant authorization is required.
Grant all privileges on *. * TO 'database name' @ '%' identified by 'database password' with grant option; this is a command TO remotely connect TO a database anywhere, you can set an instance that only promises an ip link. grant all privileges on *. * TO 'yy' @ '222. 210.228.135 'identified BY '1234
56 'with grant option; this is the command to connect to the mysql server database on the client; mysql-h 100.100.100.100-uroot-ptest we are used to installing Mysql on our own machine, then I used SQLyog and other client software to connect to the database. Today I tried to connect to a remote mysql database server. The result is a string of English letters, which indicates that the client is not licensed and cannot access the database, I searched google and used this method to solve the problem:
1. Go to mysql and create a new user kay:
2. Format: grant permission on database name. Table name user @ login host identified by "User Password ";
Grant select, update, insert, delete on *. * to kay@192.168.1.8 identified by "kaypass ";
3. view the result and execute:
Use mysql;
Select host, user, password from user;
We can see that the kay user just created already exists in the user table. The host field indicates the host to log on to. The value can be an IP address or a host name,
Changing the value of the host field to % indicates that you can log on to the mysql server as a kay user on any client machine. We recommend that you set the value to % during development.
Update user set host = '%' where user = 'Kay ';
Another method is as follows: (I have not tried it for your reference only ):
A../mysqladmin-uroot-p21century reload
./Mysqladmin-uroot-p21century shutdown
B../mysqld_safe -- user-root &
Remember: Any modification to the authorization table must be reload again, that is, step 1. If you still cannot connect from the client after the above three steps, perform the following operations to insert a record in the db table of the mysql database:
Use mysql;
Insert into db values ('2017. 168.88.234 ',' % ', 'xuys', 'y ', 'y', 'y ');
Update db set host = '%' where user = 'xuys ';
Repeat steps 2nd and 3 above. ... The remaining full text>