Mariadb – thesqldump: Got error: 2002: “Can’t connect to MySQL server on ‘x’ (code)” when trying to connect

mariadbxampp

I am trying to create a back up from a server by using XAMPP (via command prompt).

Unfortunately the error is: mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (10060)" when trying to connect. I have looked a bit around on StackOverflow (mostly0, but most questions are regarding "access denied for user at (…)" or they're talking about MYSQL. I have tried finding the files that MYSQL should have (which they're discussing) and looked whether XAMPP had these as well (it did not).

My question is:

What could possibly be causing this error? I know the name, password and host are correct.

How have I tried backing up the database?

I opened Command Prompt with administrator rights.

I navigated to the correct directory where mysqldump is located at.

I then typed in: C:\xampp\mysql\bin>mysqldump -e -uNAME -pPWD -hHOST DATABASENAME > C:\winhostMySqlD.sql

It does create the .sql file, but CMD crashes with the error mentioned above.

Best Answer

mysqldump -e -uNAME -pPWD -hHOST DATABASENAME > C:\winhostMySqlD.sql

I hope you gave this command for representational purpose, if you execute this command as it is, you will get an error. Below mentioned sample command for your reference, and possible reason for the connection error:

To backup database mydbname:

mysqldump -u username -p password -h localhost mydbname > /path/filename.sql

To backup all databases:

mysqldump -u username -p password -h localhost --all-databases > /path/filename.sql

-u username - like admin/root
-p password - like 'Admin@123'
-h hostname - where mysql server is running - like localhost/192.168.x.x (IP address)

Check my post for learn more about backup and restoration.

Some of the reasons for connection errors, listed down for your troubleshooting:

MySQL Server is not running on the server.
MySQL socket file is missing / deleted / different location.
Security Enhanced Linux is protecting mysqld process (Linux).
MySQL server is bound to single address (bind_address).
Firewall blocking MySQL port.
Access restricted to the server hosts.

Check my post for detailed troubleshooting steps for above mentioned reason and other possible causes.

Option 1

Use a replication slave off the galera cluster exclusively for creating backups. You can stop slave to ensure that all DDL operations have finished before doing a dump. Parallel dumping operations like mydumper are also possible.

Don't forget to enable log-slave-updates on the galera member you use as a master.

Option 2

Desync a galera node before doing a backup from it.

CentOS Container – Installing MariaDB Server in Docker

It sounds like you're new to Docker. You may want to spend some time reading through documentation and examples to get a better feel for how things work.

First, simply installing software doesn't necessarily make it run, and that is certainly the case with CentOS and other Red Hat variants. On a bare metal host, you would simply run systemctl start mariadb to start it, but this only works because on a regular system, the /sbin/init component of systemd is started as part of the boot process.

Inside a docker container, nothing is running by default. For example, if you were to run ps -fe on your host, you would probably see hundreds of running processes.

If you were to run:

docker run centos ps -fe

You would see:

UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 11:12 ?        00:00:00 ps -fe

Because of this, you can't start services using the normal mechanism. You will instead need to (a) start the service manually and (b) ensure that you have started it such that it will stay in the foreground (because a Docker container exits when the foreground process exits or puts itself into the background).

If you take a look at the official mariadb image, you will see that it ultimately just calls mysqld to start the service.

It sounds like you want to start mariadb inside a container and then try interacting it from within the same container. While this isn't the normal way of working with docker, you could...

$ docker run -it centos bash
[root@c1d6adf5c8bb /]# yum -y install mariadb-server
[root@c1d6adf5c8bb /]# /usr/libexec/mariadb-prepare-db-dir mariadb.service
[root@c1d6adf5c8bb /]# /usr/bin/mysqld_safe --basedir=/usr & 
[root@c1d6adf5c8bb /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

More typically, you would use a Dockerfile to build a mariadb image (or just use the official one), start it, and then use mysql on your host or in another container to interact with that mariadb instance.

Best Answer

Related Solutions

Mysql – Galera backup failing with error “thesqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table”

Option 1

Option 2

CentOS Container – Installing MariaDB Server in Docker

Related Question