Summary of problems that occur when the server remotely connects to mysql

This article summarizes the problems that arise when the server remotely connects to mysql. I would like to share it with you and learn together.

1. Solve the problem of remote connection failure under centos.

1. Check whether the firewall is closed under centos:
Through the process: ps -aux |grep firewalld
ps -ef |grep firewalld
Through the service: pgrep firewalld
systemctl status firewalld
Turn off the firewall: systemctl stop firewalld
service iptables off
2. The remote connection uses the sshd service, which is the ssh protocol. The port number is 22.
Check whether the sshd service is turned on:
Through the process: ps -aux |grep sshd
Through the service: pgrep sshd
Start the service: systemctl start sshd
Add the sshd service to the boot: chkconfig sshd on

3. Check the IP address.
　ifconfig
Under Windows, in the cmd window, ping the above IP address to see if the ping can succeed.

What should I do if I report an error?

Can’t connect to the database? The following problem occurs?
　Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock? ? ?
what to do?
First check /etc/rc.d/init.d/mysqld status to see if mysql has been started.
If it is a permission problem, change the permissions first #chown -R mysql:mysql /var/lib/mysql

　[root@localhost ~]# /etc/init.d/mysqld start
Start MySQL: [OK]
　[root@localhost ~]# mysql -uroot -p

　10038? (Remote without authorization)
　10060? (Join the security group)

Related tutorials: mysql video tutorial
2. Database operations:
1. Start mysql.
systemctl start mariadb
2. The first step is to set the password. You will be prompted to enter the password first
mysql_secure_installation
3. Create a normal user:
create user zhangsan@localhost identified by 'root';
mysql -u root -p
update user set host='%' where host='127.0.0.1';
flush privileges;
4. Authorize root user to log in remotely:
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
Flush privileges;
update user set user='root' where host='localhost';

Related tutorials: mysql video tutorial
3. Solution to mysql remote connection failure:
1. Firewall:
Set the firewall and it will not start at boot:
chkconfig firewalld off
Turn off the firewall:
Systemctl disable firewalld
Systemctl stop firewalld
Or kill the firewall process.
Check whether the firewall process exists: pgrep firewalld,
Then kill the process: kill pid
Or pkill firewalld
2. No remote permissions
Grant remote login permissions
grant all privileges on *.* to 'root'@'%' identified by '123456';
Flush privileges;

3. Start the sshd service: systemctl start sshd
Add the sshd service to boot: chkconfig sshd on

4. Access projects under Linux in Windows
Start the Apache service in Linux, that is, httpd service systemctl start httpd or: service httpd start
Access Linux from the browser under Windows Items under: Enter the IP address in the browser address bar: (You can check your own IP address through ifconfig) For example, 192.168.0.1

5. How to configure virtual domain name under Windows:
Configure local hosts file .
Location: C:\Windows\System32\drivers\etc\hosts
Just add the corresponding virtual host name.
　192.168.226.129 linux.cc

CRUD for mysql;
Add:
INSERT INTO `user` (`id`, `username`, `passwd`, `sex`) VALUES ('1', 'zhangsan', '12345', '')
Update:
UPDATE `user` SET `sex`='女' WHERE (`id`='2')
Delete:
DELETE FROM `user` WHERE (`id`='2')
Query:
　SELECT * FROM `user`;

6. Virtual Host
　
　ServerAdmin webmaster @dummy-host.example.com
　DocumentRoot /www/docs/dummy-host.example.com
　ServerName dummy-host.example.com
　ErrorLog logs/dummy-host.example.com-error_log
　CustomLog logs/dummy-host.example.com-access_log common
　

7. Set startup: switch the default graphical interface to the command line mode
　　Modify to Command mode multi-user.target：
　[root@localhost ~]# systemctl set-default multi-user.target
　rm '/etc/systemd/system/default.target'
　ln -s ' /usr/lib/systemd/system/multi-user.target' '/etc/systemd/system/default.target'
　[root@localhost ~]# systemctl get-default
　Get: multi-user. target: achieve the effect.
Switch to: graphical mode: systemctl set-default graphical.target

8. Configuration file description
A: httpd.conf The main configuration file of Apache.
B: php.ini PHP’s main configuration file
D: httpd-vhost.conf When configuring the virtual host, the changed file
E: my.cnf Mysql’s main configuration file under Linux

Note: The main configuration file mysql under Windows my.ini

Related tutorials: PHP video tutorial

The above is the detailed content of Summary of problems that occur when the server remotely connects to mysql. For more information, please follow other related articles on the PHP Chinese website!