Cannot get MySQL to accept mc.sql
Moderator: Forum Moderators
Cannot get MySQL to accept mc.sql
This is a fresh Mythtv setup on Mint 20.3
$ mysql -uroot -p gets me into mysql.
$mysql -uroot -p < mc.sql does not.
I decided to try the contents line by line.
The second line generated an error msg:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH 'mythtv';
ERROR 1524 (HY000): Plugin 'mythtv' is not loaded
Yes I confess that I replaced 'localhost' with '192.168.1.4' .
I had also changed password parameters:
[mysql> SHOW VARIABLES LIKE 'validate_password%';
+-----------------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+------------------------------------------------------+-------+
I am at a loss
$ mysql -uroot -p gets me into mysql.
$mysql -uroot -p < mc.sql does not.
I decided to try the contents line by line.
The second line generated an error msg:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH 'mythtv';
ERROR 1524 (HY000): Plugin 'mythtv' is not loaded
Yes I confess that I replaced 'localhost' with '192.168.1.4' .
I had also changed password parameters:
[mysql> SHOW VARIABLES LIKE 'validate_password%';
+-----------------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+------------------------------------------------------+-------+
I am at a loss
Re: Cannot get MySQL to accept mc.sql
Check what version of SQL Mint is using. If it's MySQL v8, then the
syntax has changed. mc.sql shoudl look like this:
Of course you can replace localhost with remote IPs as you already tried.
syntax has changed. mc.sql shoudl look like this:
Code: Select all
CREATE DATABASE IF NOT EXISTS mythconverg;
CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@localhost;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost;
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Re: Cannot get MySQL to accept mc.sql
Thanks Bill.
At least it has moved forward to this old chestnut.
mysql -uroot -p < mc.sql
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
I have mysql-server installed.
I cannot find the lengthy version of my.cnf on my system that I have a listing of from 14 years ago!
At least it has moved forward to this old chestnut.
mysql -uroot -p < mc.sql
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
I have mysql-server installed.
I cannot find the lengthy version of my.cnf on my system that I have a listing of from 14 years ago!
Re: Cannot get MySQL to accept mc.sql
By default, mysql will use --host=localhost, which can (I'd argue should) use a socket.
Try adding the --host=theHostNameOrIP switch to tell it where your DB lives. Or, run it on
that host directly.
Try adding the --host=theHostNameOrIP switch to tell it where your DB lives. Or, run it on
that host directly.
Re: Cannot get MySQL to accept mc.sql
I tried every combination that I could without success.
I then tried to create mysqld.sock at /var/run/mysqld also without success.
I then created it at /etc/mysqld and edited /etc/mysqld/mysql.cnf adding the following to the foot:
######## Added by AS 15/01/2022 ########
[mysqld]
socket = /etc/mysql/mysqld.sock
Still no success.The command insisted it was searching at '/var/run/mysqld/mysqld.sock'
I went back and ran:
$ mysql --host=albury.asandco -uroot -p < mc.sql
and got:
ERROR 2003 (HY000): Can't connect to MySQL server on 'albury.asandco:3306' (113)
I then tried to create mysqld.sock at /var/run/mysqld also without success.
I then created it at /etc/mysqld and edited /etc/mysqld/mysql.cnf adding the following to the foot:
######## Added by AS 15/01/2022 ########
[mysqld]
socket = /etc/mysql/mysqld.sock
Still no success.The command insisted it was searching at '/var/run/mysqld/mysqld.sock'
I went back and ran:
$ mysql --host=albury.asandco -uroot -p < mc.sql
and got:
ERROR 2003 (HY000): Can't connect to MySQL server on 'albury.asandco:3306' (113)
Re: Cannot get MySQL to accept mc.sql
The socket is created when the MySQL daemon starts. I <think> you're still trying to do this from the remote frontend.
Don't. I suspect you don't have permissions there.
On the backend (where the DB is) check the existing user permissions:
Don't. I suspect you don't have permissions there.
On the backend (where the DB is) check the existing user permissions:
Code: Select all
$ mysql --user=mythtv --host=localhost --password mythconverg
mysql> SHOW GRANTS;
+-----------------------------------------------------------------+
| Grants for mythtv@localhost |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
exit
Re: Cannot get MySQL to accept mc.sql
This is a fresh installation of Mint 20.3 only a couple of days ago.
It has both mythtv backend and frontends installed.
$ mysql --user=mythtv --host=localhost --password mythconverg
Enter password:
Which password? mythtv or none produce the same result:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
It has both mythtv backend and frontends installed.
$ mysql --user=mythtv --host=localhost --password mythconverg
Enter password:
Which password? mythtv or none produce the same result:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Re: Cannot get MySQL to accept mc.sql
If the socket isn't there, then typically the server isn't running. Try: systemctl status mysql
The password would be the one in config.xml.
Eventually, to add a user, the root mysql user is required. The method for accessing it
seems to be distribution dependant. This 'works for me':
But let's see the output of the SHOW GRANTS command 1st. Include the mysql version
that appears in the output please when mysql starts.
The password would be the one in config.xml.
Eventually, to add a user, the root mysql user is required. The method for accessing it
seems to be distribution dependant. This 'works for me':
Code: Select all
sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
that appears in the output please when mysql starts.
Re: Cannot get MySQL to accept mc.sql
$ sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
[sudo] password for me:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
I never get to SHOW GRANTS.
I have just discovered mysqld.cnf in /etc/mysqld/mysqld.conf.d but the basic settings (including the socket address) are commented out.
So where does MySQL grab its configuration parameters from?
[sudo] password for me:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
I never get to SHOW GRANTS.
I have just discovered mysqld.cnf in /etc/mysqld/mysqld.conf.d but the basic settings (including the socket address) are commented out.
So where does MySQL grab its configuration parameters from?
Re: Cannot get MySQL to accept mc.sql
The lines that are commented out are the defaults. No need to touch them.
In fact, NEVER make any changes to that file because a package manager
can (and does) update things there and your changes get blown away.
What did the command above: systemctl status mysql show?
In fact, NEVER make any changes to that file because a package manager
can (and does) update things there and your changes get blown away.
What did the command above: systemctl status mysql show?
Re: Cannot get MySQL to accept mc.sql
$ systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:
Active: activating (start) since Sat 2022-01-15 22:18:26 GMT; 2s ago
Process: 374880 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code
Main PID: 374888 (mysqld)
Status: "Server startup in progress"
Error: 99 (Cannot assign requested address)
Tasks: 11 (limit: 19021)
Memory: 353.0M
CGroup: /system.slice/mysql.service
└─374888 /usr/sbin/mysqld
Jan 15 22:18:26 albury.asandco systemd[1]: Starting MySQL Community Server...
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:
Active: activating (start) since Sat 2022-01-15 22:18:26 GMT; 2s ago
Process: 374880 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code
Main PID: 374888 (mysqld)
Status: "Server startup in progress"
Error: 99 (Cannot assign requested address)
Tasks: 11 (limit: 19021)
Memory: 353.0M
CGroup: /system.slice/mysql.service
└─374888 /usr/sbin/mysqld
Jan 15 22:18:26 albury.asandco systemd[1]: Starting MySQL Community Server...
Re: Cannot get MySQL to accept mc.sql
sudo systemctl stop mysql
grep -ir bind.address /etc/mysql
I don't think the grep is going to find a problem, but it's worth checking.
sudo systemctl start mysql
sudo cat /var/log/mysql/error.log
No need to paste the whole log, just look for the timestamps from you just started the daemon above.
grep -ir bind.address /etc/mysql
I don't think the grep is going to find a problem, but it's worth checking.
sudo systemctl start mysql
sudo cat /var/log/mysql/error.log
No need to paste the whole log, just look for the timestamps from you just started the daemon above.
Re: Cannot get MySQL to accept mc.sql
alan@albury:~$ sudo systemctl stop mysql
alan@albury:~$ grep -ir bind.address /etc/mysql
/etc/mysql/conf.d/mythtv.cnf-vanilla:#bind-address=::
/etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.4
grep: /etc/mysql/debian.cnf: Permission denied
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 127.0.0.1
alan@albury:~$ sudo systemctl start mysql
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
$ sudo cat /var/log/mysql/error.log
2022-01-15T11:25:23.214821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 1224
Is that OK?
alan@albury:~$ grep -ir bind.address /etc/mysql
/etc/mysql/conf.d/mythtv.cnf-vanilla:#bind-address=::
/etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.4
grep: /etc/mysql/debian.cnf: Permission denied
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 127.0.0.1
alan@albury:~$ sudo systemctl start mysql
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
$ sudo cat /var/log/mysql/error.log
2022-01-15T11:25:23.214821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 1224
Is that OK?
Re: Cannot get MySQL to accept mc.sql
Yes, that's OK.
Delete the line: /etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.4
Restart mysql.
I'd check systemctl status --no-pager mysql.service
Delete the line: /etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.4
Restart mysql.
I'd check systemctl status --no-pager mysql.service
Re: Cannot get MySQL to accept mc.sql
Thanks. That got me in.
Going back to an earlier request:
$ mysql --user=mythtv --host=localhost --password mythconverg
returned:
ERROR 1045 (28000): Access denied for user 'mythtv'@'localhost' (using password: YES)
Not surprising as I had not been able to get mysql to read mc.sql, so I tried again:
This time it returned ERROR 1819 (HY000) at line 2: Your password does not satisfy the current policy requirements.
The line is:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH mysql_native_password;
I repeated the exercise with:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH 'mythtv';
That returned:
ERROR 1524 (HY000) at line 2: Plugin 'mythtv' is not loaded
Going back to an earlier request:
$ mysql --user=mythtv --host=localhost --password mythconverg
returned:
ERROR 1045 (28000): Access denied for user 'mythtv'@'localhost' (using password: YES)
Not surprising as I had not been able to get mysql to read mc.sql, so I tried again:
This time it returned ERROR 1819 (HY000) at line 2: Your password does not satisfy the current policy requirements.
The line is:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH mysql_native_password;
I repeated the exercise with:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH 'mythtv';
That returned:
ERROR 1524 (HY000) at line 2: Plugin 'mythtv' is not loaded