Tag Archives: mysql

When using SSH to log in to MySQL, Navicat reported an error: expected key exchange group packet from server

1、 Navicat SSH connection mode

Because the company uses alicloud’s RDS, it needs to connect to the database through the intranet (it does not provide direct public network access). At this time, you can use the SSH method of Navicat to connect to the RDS through a springboard machine

2、 Connection error and solution

Error: SSH: expected key exchange group packet from server

Solution:

1. Upgrade Navicat

2. Modifying the configuration of sshd

[root@Manager ~]#vim /etc/ssh/sshd_config  #Edit the sshd configuration file and add at the end
KexAlgorithms [email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group14-sha1

 

The jdbc driver imported from idea into MySQL appears “Java. Lang. classnotfoundexception: com. Mysql. CJ. JDBC. Driver”

 

When we use java to operate MySQL database in idea, the following results will appear:

Exception in thread “main” java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
at java.net.URLClassLoader.findClass(URLClassLoader. java:382 )
at java.lang.ClassLoader.loadClass(ClassLoader. java:418 )
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher. java:355 )
at java.lang.ClassLoader.loadClass(ClassLoader. java:351 )
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class. java:264 )
at src.mySql.Jdbc.main(Jdbc. java:10 )

1、 General solution

1. JDBC download link

https://dev.mysql.com/downloads/connector/j/

2. Select the content to download and download it

Select platform independent as the operating system. For the two records in the list below, the suffix tar.gz is the Linux version and the suffix. Zip is the Windows version

Here, download the jdbc driver of Windows version, version 8.0.18

Skip login and click the content in the red box to download

3. Import driver into Java project

In idea, click file — project structure

Module, dependencies tab

Click the plus sign (+) on the far right and select jars or directions

2、 Still import driver failed

1. In idea, click file — project structure

2. Select the SDKs, click the plus sign (+) on the far right, select the jdbc driver of Windows version we downloaded before, and then click OK to confirm

3. Finally, we can use this driver by connecting to MySQL database on the idea

Example:

package src.mySql;

import java.sql.*;

public class Jdbc {
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            long start = System.currentTimeMillis();

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1",
                    "root", "root");
            long end = System.currentTimeMillis();
            System.out.println(conn);
            System.out.println("Time to establish connection: " + (end - start) + "ms milliseconds");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

Results: the results were as follows

After installing mysql, centos7 can’t start the service, and prompts unit not found

Phenomenon:

Start MySQL database prompt:

Failed to start mysqld.service: Unit not found

MySQL is no longer supported in centos7. Even if you have installed it, centos7 still says it’s disgusting

 

 

Solutions

Maria dB, an open source version of MySQL

(Maria DB is like the shadow version of MySQL. Maria DB is a branch version of MySQL, not a folk version. The functions provided by Maria dB are fully compatible with MySQL.)

1. Installation: 

yum install -y mariadb-server

2.start maria DB serves:

systemctl start mariadb.service

(Note: since CentOS 7. X, CentOS has started to use SYSTEMd service instead of daemon, and the original commands related to the start-up and management of system services are all replaced by systemctl commands.)

3. Add to boot auto start: 

systemctl enable mariadb.service

Initialize database configuration

mysql_secure_installation

The first step is to set the password. You will be prompted to enter the password first

Set password

Enter current password for root (enter for none):<– Direct return

Set root password?[ Y/n] <– Whether to set the root password, enter y and press enter or press enter directly
new password: & lt; — Set the password of the root user
re enter new password: & lt; — Enter the password you set again
for other configurations

Remove anonymous users?[ Y/n] <– Do you want to delete anonymous users?Y enter

Disallow root login remotely?[ Y/n] <– Do you want to disable root remote login?N enter

Remove test database and access to it?[ Y/n] <– Delete test database, y enter

Reload privilege tables now?[ Y/n] <– Do you want to reload the permission table?Y enter

Complete the initialization of MariaDB, and then test the local login

Location of configuration file: VIM/etc/my.cnf.d/mysql-clients.cnf

Turn on remote access

Log in to the database locally and execute the following command:

First of all, configure the users who are allowed to access, and give the users permission in the way of authorization

GRANTALLPRIVILEGESON*.*TO'root'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;

Finally, after configuring the permissions, you should not forget to refresh them to make them take effect. Note: root is the user who logs in to the database, 123456 is the password for logging in to the database, * means that any host from any source has great permissions anyway

flushprivileges;

[Solved] Centos7 Install MYSQL Error: Failed to start mariadb.service: Unit not found.

Solution:

First, you need to install MariaDB server

yum install -y mariadb-server

Start the service

systemctlstartmariadb.service

Add to boot

systemctlenablemariadb.service

Make some security settings, and modify the database administrator password

MySQL installation error — failed to find valid data directory

After chopping hands, the fraud call came before the express delivery was received. How to improve the privacy and security of e-commerce>>>

Running environment: windows10
database version: MySQL. 8.0.12
installation mode: RPM package direct installation

Problem Description:
the corresponding database storage directory cannot be found during MySQL initialization

Error code:

2018-10-13T03:29:24.179826Z 0 [System] [MY-010116] [Server] D:\Program Files\MySQL\bin\mysqld.exe (mysqld 8.0.12) starting as process 7420
2018-10-13T03:29:24.205939Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2018-10-13T03:29:24.207560Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-10-13T03:29:24.209780Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-10-13T03:29:24.213334Z 0 [System] [MY-010910] [Server] D:\Program Files\MySQL\bin\mysqld.exe: Shutdown complete (mysqld 8.0.12)  MySQL Community Server - GPL.

Solutions:
1. Manually delete the data folder created by yourself
2. Then enter the bin directory under the administrator’s CMD and remove your mysqld service

D:\Program Files\MySQL\bin>mysqld -remove MySQL
Service successfully removed.

1

2

3. Execute mysqld — initialize execute in the bin directory of CMD
the program will create the data folder and corresponding files in the dynamic MySQL folder
4. Execute in the bin directory, mysqld — install, and install the mysqld service
5. Run net start MySQL in the bin directory to start the MySQL service

Link to the original text: https://blog.csdn.net/mukouping82/article/details/81105831

The solution of MySQL’s fatal error encoded during command execution

After chopping hands, the fraud call came before the express delivery was received. How to improve the privacy and security of e-commerce>>>

MySQL reports fatal error accounted during command execution

Reference article:

(1) The solution of MySQL’s fatal error encoded during command execution

（2） https://www.cnblogs.com/qiywtc/p/4813167.html

Let’s make a note.

Mysql Auto Downtime ERROR: InnoDB: Cannot allocate memory for the buffer pool [Solved]

When I went to work this morning, I found that I couldn’t access the website built by WordPress. I reported the following error:

Error establishing a database connection

Quickly log on to your own Alibaba cloud server (centos7.4) to view the MySQL service. Through PS – EF | grep mysql, you find that there is no MySQL process

The first thing to do is to check the MySQL log and find the corresponding error exception through VI/var/log/mysqld.log

2018-12-26T02:06:16.148626Z 0 [Note] Event Scheduler: Purging the queue. 0 events
"/var/log/mysqld.log" 623L, 59293C
2019-02-01T07:56:07.052989Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-02-01T07:56:07.052991Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-02-01T07:56:07.052994Z 0 [Note] InnoDB: Using Linux native AIO
2019-02-01T07:56:07.053336Z 0 [Note] InnoDB: Number of pools: 1
2019-02-01T07:56:07.053443Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-02-01T07:56:07.055009Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-02-01T07:56:07.055061Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-02-01T07:56:07.055067Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-02-01T07:56:07.055073Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-02-01T07:56:07.055081Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-02-01T07:56:07.055085Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-02-01T07:56:07.055088Z 0 [ERROR] Failed to initialize builtin plugins.
2019-02-01T07:56:07.055091Z 0 [ERROR] Aborting

2019-02-01T07:56:07.055108Z 0 [Note] Binlog end
2019-02-01T07:56:07.055159Z 0 [Note] Shutting down plugin 'CSV'
2019-02-01T07:56:07.055380Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

It can be seen that there is not enough memory, resulting in the oom problem. Continue to view the memory with free – M and find the following:

free -m
              total        used        free      shared  buff/cache   available
Mem:            992         270         107          50         614         496
Swap:             0           0           0

There is not enough memory, only 107m space is available, swap free space is 0

So there are two solutions:

1: Modify the/etc/my.inf file to delete InnoDB_ buffer_ pool_ Size down

innodb_buffer_pool_size = 32M

2: Add swap file

For specific steps, see Adding swap swap space on CentOS 7

So far, the problem has been solved

—————————————–

There is no fear of problems, the solution is very important

mysqlmmap(137428992 bytes) failed; errno 12,Cannot allocate memory for the buffer pool

Why can’t you stop buying 618?From the technical dimension to explore>>>

After starting MySQL in the way of ‘systemctl start mysqld. Service’ for a period of time, it was found that it could not be started suddenly, and trying to restart it could not solve the problem. When troubleshooting the problem, the command ‘systemctl status mysqld. Service’ and ‘journalctl – Xe’ were used to check the problem, but there was no result. After checking ‘/ var/log/mysqld. Log’, it was found that the error information in the log was as follows:

2019-02-07T00:33:21.731341Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 1

28M

2019-02-07T00:33:21.731439Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12

2019-02-07T00:33:21.731450Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

2019-02-07T00:33:21.731464Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

2019-02-07T00:33:21.731477Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.

2019-02-07T00:33:21.731483Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

2019-02-07T00:33:21.731489Z 0 [ERROR] Failed to initialize builtin plugins.

2019-02-07T00:33:21.731493Z 0 [ERROR] Aborting

Looking up the information on the Internet, some people say that swap is not enough. Using the ‘free’ command to check, it is found that the spare swap is indeed 0, but it is very strange that the spare swap I bought for vultr and ECS using ‘free’ is 0. So why did the MySQL service deployed by vultr hang up and ECS not?Later, I used the command ‘sudo DD if =/dev/zero of =/swapfile BS = 1m count = 1024’ to add swap and found that it didn’t work. My spare swap was still 0

Looking at the data, some people said that in the “my. CNF”, the “InnoDB” would be changed_ buffer_ pool_ Open the comment, restart the service, and report an error

Later, I used the InnoDB in my. CNF_ buffer_ pool_ Size = 64M ‘is lowered to 64M, and then the service is restarted. MySQL starts successfully

Conclusion: my server configuration may be low, which makes the normal ‘initializing buffer pool = 128’ of mysql5.7 unable to be allocated successfully. Just reduce the configuration
`

question 2: changed limits: Max_ open_ files: 5000 (requested 5010)

Whether the command ‘ulimit – n’ is restricted by the Linux system

Check the ‘limitnofile’ under ‘VIM/usr/lib/SYSTEMd/system/mysqld. Service’ to see if there is a 5000 limit. Just adjust it to 65535

Reference [here]（ http://blog.itpub.net/20893244/viewspace-2152685/ )

problem 3: Alibaba cloud server MySQL is a perfect solution to automatically stop hanging up and restart

Solution: [here]（ https://blog.csdn.net/xuz0917/article/details/79100834 )

[here]（ https://jingyan.baidu.com/article/da1091fb032cad027949d64e.html )

problem 4: IP address’ x.x.x.x ‘could not be resolved: name or service not known

solution: [here]（ https://www.jb51.net/article/70893.htm )

Configure ‘skip name resolve’ in ‘my. CNF’
file`

Question 5: got error: 1045: access denied for user ‘root’ @’localhost ‘ ( using password: )

Solution: Remove ‘skip name resolve’ from ‘my. CNF’

Liu Shi’s Novels