Install MySQL under Ubuntu
You need to build a website, except for the web, which is the database, and the LAMP suite is "Linux+Apache+Mysql+PHP. These four software forms a package tool software that can run the website.
1、 MySQL installation
sudo apt-get install mysql-server mysql-client
Enter the password of the root user: 123456, press Enter
enter:
sudo apt install libmysqlclient-dev
Completed the MySQL installation!
2、 Test whether the installation is successful
sudo netstat -tap | grep mysql
enter:
mysql -uroot -p
View the database:
show databases;
Select the mysql database:
use mysql;
Check the "user" table of the database "mysql" to see if the host corresponding to root is set correctly:
select user, host from user;
If host is localhost, remote access is not allowed; it needs to be changed to% to indicate remote access.
The% means to allow all ip remote access, if you need to specify a specific ip, just write the specific ip
Update the user table:
update user set host ='%' where user ='root' limit 1;
Force refresh permissions
flush privileges;
3、 Connect to MySQL
We use Navicat under Windows7 to connect to MySQL under Ubuntu
Use # sudo ifconfig to get the ip address of Ubuntu (make sure that Ubuntu and Windows are on the same network segment!)
Ubuntu's ip is 192.168.1.127, remember!
Open Navicat under Windows, connect, MySQL,
Enter the password of ip and root, click test connection
It prompts that you cannot connect to Ubuntu's MySQL. Generally, you can connect to the remote server's MySQL. If there is a problem, the big problem will be the server port and authorization problem.
Solution:
First we pass
①：
netstat -an|grep 3306
To check whether the default port 3306 of mysql is open, which ip is allowed to use, if you find that there is 127.0.0.1 in front, it means that port 3306 can only be used by the local ip
So we need
②: Open the mysql configuration file
vim /etc/mysql/mysql.conf.d/mysqld.cnf
If MariaDB is installed, the mysql configuration file is in:
vim /etc/mysql/mariadb.conf.d/50-server.cnf
Comment out bind-address = 127.0.0.1
③: Enter mysql, authorize remote users,
grant all privileges on *.* to 'root'@'%' identified by 'xxxxxx';
Here root is the user you log in remotely, xxxxxx is the password you use to log in, and then you can see in the mysql data table that your user has been added to the user table
Then restart mysql:
service mysql restart
or:
/etc/init.d/mysql stop
/etc/init.d/mysql start
Then enter Navicat to reconnect to test, this time Ok!
Click OK to confirm.
4、 MySQL operation
**Log in to the database: **
mysql -u root -p
**User authorization: **
grant all privileges on *.* to root@"%" identified by "root"with grant option;
The first command is explained as follows, .: The first * represents the database name; the second * represents the table name. This means that all tables in all databases are authorized to users. root: Grant the root account.
"%": Indicates that the authorized user IP can be specified, which means that any IP address can access the MySQL database. "Password": Assign the password corresponding to the account, where the password is replaced with your mysql root account password.
**Refresh permission information: **
flush privileges;
**Start the MySQL service: **
sudo start mysql
**Stop MySQL service: **
sudo stop mysql
**Modify the MySQL administrator password: **
sudo mysqladmin -u root password newpassword
Set up remote access (under normal circumstances, port 3306 occupied by mysql is only monitored on IP 127.0.0.1, and access to other IPs is denied (you can view it through netstat). Cancel local monitoring
Listen to need to modify the my.cnf file:):
sudo vim /etc/mysql/my.cnf
bind-address = 127.0.0.1 //Find this content and comment
Analysis of the directory structure after MySQL installation (this structure is only for online installation using apt-get install):
Database storage directory: /var/lib/mysql/
Related configuration file storage directory: /usr/share/mysql
The relevant command storage directory: /usr/bin (mysqladmin mysqldump and other commands)
Startup script storage directory: /etc/rc.d/init.d/
Recommended Posts