Mysql not connecting urgent

S

ssgill

Verified User
Joined
May 9, 2012
Messages
168
Hello, since this morning mysql is not connecting. Its running but incorrect socket

mysqld.service - MySQL database server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; vendor preset: enabled)
Active: activating (start) since Thu 2023-05-11 11:22:38 MDT; 451ms ago
Process: 14281 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 14282 ExecStartPre=/bin/sh -c [ ! -e /usr/local/mysql/bin/galera_recovery ] && VAR= || VAR=`cd /usr/local/mysql/bin/..; /usr/local/mysql/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION
Main PID: 14307 (mysqld)
Status: "Starting final batch to recover 214 pages from redo log"
Tasks: 15 (limit: 4915)
Memory: 74.1M
CGroup: /system.slice/mysqld.service
└─14307 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock

May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Uses event mutexes
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Number of pools: 1
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Using SSE2 crc32 instructions
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Completed initialization of buffer pool
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=140993460115
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Starting final batch to recover 214 pages from redo log.
Click to expand...

my.cnf

[mysqld]
max_allowed_packet=64M
local-infile=0
innodb_file_per_table
sql_mode=""
socket=/usr/local/mysql/data/mysql.sock


[client]
socket=/usr/local/mysql/data/mysql.sock
Click to expand...

ls -la /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 May 11 11:28 /var/lib/mysql/mysql.sock
Click to expand...

netstat -lnp | grep mysql
// empty no response
Click to expand...

ps ax | grep mysql
14322 pts/1 S+ 0:00 systemctl status mysqld
26544 ? Ssl 0:00 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock
26582 pts/0 S+ 0:00 grep mysql
27002 pts/2 S 0:00 su -l mysql -s /bin/bash
Click to expand...

I have tried changing the socket in my.cnf to /var/lib/mysql/mysql.sock, but still not able to connect.

Thanks for your help.
 
wait till restore finish:

May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=140993460115
May 11 11:22:38 matrix.mydomain.com mysqld[14307]: 2023-05-11 11:22:38 0 [Note] InnoDB: Starting final batch to recover 214 pages from redo log.
 
Thanks for the quick reply, but it keeps on crashing during restore and i guess its one database that's doing this. How can i remove this one database for new and see if it mysql goes on line.
 
Do you have enough harddisk space ? I have seen MySQL doing weird things of that in the past
 
Status

systemctl status mysqld
â— mysqld.service - MySQL database server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; vendor preset: enabled)
Active: activating (auto-restart) (Result: signal) since Thu 2023-05-11 11:53:29 MDT; 1s ago
Process: 9589 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 9590 ExecStartPre=/bin/sh -c [ ! -e /usr/local/mysql/bin/galera_recovery ] && VAR= || VAR=`cd /usr/local/mysql/bin/..; /usr/local/mysql/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=
Process: 9614 ExecStart=/usr/local/mysql/bin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock (code=killed, signal=ABRT)
Process: 9647 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 9614 (code=killed, signal=ABRT)
Status: "Taking your SQL requests now..."
Click to expand...

df -lh

Filesystem Size Used Avail Use% Mounted on
udev 7.8G 0 7.8G 0% /dev
tmpfs 1.6G 109M 1.5G 7% /run
/dev/mapper/matrix--vg-root 23G 12G 11G 52% /
tmpfs 7.8G 196K 7.8G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
/dev/sda2 471M 84M 363M 19% /boot
/dev/mapper/matrix--vg-var 9.1G 7.8G 841M 91% /var
/dev/mapper/matrix--vg-tmp 1.8G 236K 1.7G 1% /tmp
/dev/sda1 511M 5.8M 506M 2% /boot/efi
/dev/mapper/matrix--vg-home 1.1T 423G 585G 42% /home
tmpfs 1.6G 0 1.6G 0% /run/user/1000
tmpfs 1.6G 0 1.6G 0% /run/user/1004
Click to expand...

Thanks
 
It start's and then crashed system status

mysqld.service - MySQL database server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2023-05-11 12:05:00 MDT; 405ms ago
Process: 16970 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 16971 ExecStartPre=/bin/sh -c [ ! -e /usr/local/mysql/bin/galera_recovery ] && VAR= || VAR=`cd /usr/local/mysql/bin/..; /usr/local/mysql/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION
Process: 17028 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 16995 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 4915)
Memory: 107.3M
CGroup: /system.slice/mysqld.service
├─16995 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock
└─17029 addr2line -C -f -e /lib/x86_64-linux-gnu/libpthread.so.0

May 11 12:05:00 matrix.mydomain.com mysqld[16995]: 2023-05-11 12:05:00 0 [Note] Plugin 'FEEDBACK' is disabled.
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: 2023-05-11 12:05:00 0 [Note] Server socket created on IP: '::'.
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: 2023-05-11 12:05:00 0 [Note] Reading of all Master_info entries succeeded
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: 2023-05-11 12:05:00 0 [Note] Added new Master_info '' to hash table
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: 2023-05-11 12:05:00 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: Version: '10.4.27-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: 2023-05-11 12:05:00 0 [Note] InnoDB: Buffer pool(s) load completed at 230511 12:05:00
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: mysys/stacktrace.c:175(my_print_stacktrace)[0x56377dc42abe]
May 11 12:05:00 matrix.mydomain.com systemd[1]: Started MySQL database server.
May 11 12:05:00 matrix.mydomain.com mysqld[16995]: sql/signal_handler.cc:232(handle_fatal_signal)[0x56377d686c7f]
lines 1-24/24 (END)
Click to expand...
 
you can try force recovery one of modes, step by step lower it. there must be more info in mysql log.
 
I am going to get databases from the backup, what would be the best way to start over. Remove current mysql and reinstall, debian latest.

Thanks
 
ssgill said:
Followed but old data still exists, any way to remove old databases.
Click to expand...
Where do they exist? Did you stop mysql/mariadb first and then to this?
mv /var/lib/mysql /var/lib/mysql.old
or if you have old backups delete the /var/lib/mysql directory?
If yes, then all old databases should be one. Remember to set mysql_backup to no before installing again.

Only other backups can exist here:
/usr/local/directadmin/custombuild/mysql_backups/
 
Thanks, everything is going in circles. i did it before but i guess i missed one step my mysql directory is in home

/home/mysql
Click to expand...
its symlinked to /var/lib/mysql

so i moved /home/mysql-backup
remove symlink

and reinstalled and mysql is running now.

Thanks for your support. Staring backup
 
You must log in or register to reply here.
Back
Top