I once found the following error when installing MySQL ODBC Driver 5.3.7:Continue reading “Solution of error 1918 when installing MySQL ODBC Driver”
Once in the FreeRADIUS logs I noticed a MySQL error:
Table ‘./radius/radacct’ is marked as crashed and last (automatic?) repair failed
As it turned out, the radacct table was damaged, since the data there were not particularly important, then the entire table was cleaned.
You can clean up via phpMyAdmin or SQL query:
truncate table TableName
A bit later for the experiment I decided to break the whole database, took another large table in general from another application, about 8 gigabytes in size and 80 million lines.
I applied to it SQL query to clean up old rows before the date specified in the query and rebooted at that moment MySQL, the request was interrupted, the database was left intact, executed the request to optimize the database and again rebooted MySQL, eventually got a corrupted database and a similar error:
#144 – Table ‘name’ is marked as crashed and last (automatic?) repair failed
To restore the database, you must stop the MySQL server (if the table is not used, then you can not stop it):
sudo service mysql stop
Let’s move to the directory with the database:
Execute the command to restore the specified table:
myisamchk -r -o -f -v $TABLE_NAME
Upon completion, if you stopped the MySQL server, then run it:
sudo service mysql start
Similarly, on the test, also to speed up the process, the table was restored by copying it to another more powerful server, namely three files /var/lib/mysql/$DATABASE_NAME/ ($TABLE_NAME.MYD, $TABLE_NAME.MYI, $TABLE_NAME.frm).
Here is an example of changing the MySQL encoding of a database and tables.
Before any actions on important data it is necessary to make a backup copy, for example:
mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz
For the test, we will connect to MySQL and create a couple of new databases without specifying the encoding and specifying:
mysql -u root -p CREATE DATABASE test_db1; CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;
Create a test table in the first database and see its encoding:
USE test_db1; CREATE TABLE users ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP ); show table status like 'users';
Create a test table in the second database and see its encoding:
USE test_db2; CREATE TABLE users ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP ); show table status;
Let’s also look at the encoding of both databases:
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1"; SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";
To see the encoding of a column in a specific table, you can do this:
SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "test_db1" AND table_name = "users" AND column_name = "firstname";
In my case, the table in the first database was encoded with latin1_swedish_ci, since it is standard, and in the second one utf8_general_ci, since I specified it beforehand.
You can see the table of possible encodings by such requests:
show collation; show collation like 'utf8%'; show collation like 'latin1%';
View existing databases as follows:
View existing tables in the database:
USE test_db1; show tables;
Now we change the encoding of the first database and its tables to utf8 and immediately check:
ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE test_db1; ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; show table status;
If you need to change the encoding in a sql file, open it in the Notepad ++ editor, for example, convert it to UTF-8/without BOM, and if the encoding in SET NAMES is specified at the beginning of the file, change it there, then you can import the file into the database.
To create a user, we first connect to the MySQL server console:
Let’s see what users are:
select * from mysql.user; select user,host from mysql.user;
Create a user (where localhost is specified from where the user can connect, you can specify the IP address, localhost – from the local machine where the MySQL server itself, or % from any addresses):
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
If you intend to connect not only locally, you need to comment out the line in my.cnf:
#bind-address = 127.0.0.1
And restart the MySQL server:
sudo service mysql restart
After that, I recommend restricting access to MySQL using IPTables.
See also – Configuring IPTables
To assign the newly created user unlimited permissions to a specific database, execute the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';
If necessary on all bases:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
You can specify specific access rights:
GRANT SELECT ON database_name.* TO 'user'@'localhost'; GRANT SELECT, INSERT ON database_name.table_name TO email@example.com;
If you want to create a new database:
CREATE DATABASE database_name;
For the changes to take effect, execute:
You can delete the user as follows:
DROP USER 'user'@'localhost';
Example of viewing privileges:
SHOW GRANTS FOR 'user'@'localhost'; SHOW GRANTS; SELECT * FROM information_schema.user_privileges;
Below are examples of importing and exporting MySQL databases from a Linux terminal.
Export the database to a file like this:Continue reading “Import and export MySQL databases”
I noticed once when importing a sql file the following error:
ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’
It arises because new versions of MySQL server use strict mode and parameters such as NO_ZERO_DATE do not allow entering date values like ‘0000-00-00’ into the database.
Connect to mysql server:
mysql -u root -p
Execute a query that displays the values of sql_mode:
show variables like 'sql_mode';
Copy the string with these values and exit mysql:
Open the configuration file for example in the text editor nano (Ctrl+X for exit, y/n for saving or canceling the changes):
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
I did not have sql_mode = in the file, so at the end of the file I inserted the line with the previously copied values, removing NO_ZERO_IN_DATE, NO_ZERO_DATE from it, in my case, the following happened:
Restart mysql to apply the changes:
sudo service mysql restart
Done, now when importing this error should not be.
Here is an example of a script written in PHP, for sending SMS messages through the Goip4 gateway.
The script receives data from the SQL database with a query and alternately sends SMS to each number, and also writes an entry about sending it to a special sms table.
Continue reading “SMS sending script via Goip4 gateway”
To see a table with a list of databases and their size in megabytes, you can execute a SQL query:Continue reading “Monitoring the size of a MySQL database or table in Zabbix”
wait_timeout – The number of seconds that the server waits for activity in a non-interactive connection before closing it.
At the time of connection, wait_timeout is taken from the global value wait_timeout or interactive_timeout depending on the client type (as defined by the CLIENT_INTERACTIVE connect option for mysql_real_connect ())
Connect to MySQL and see the current value:
mysql -u USER -p show variables like "wait_timeout"; show variables like "interactive_timeout"; quit;
By default, the values wait_timeout and interactive_timeout are 28800 seconds = 8 hours.
You can set minimum 1, maximum – 31536000, maximum (for Windows) – 2147483.
You can change the value of wait_timeout by executing the SQL query, for example:
set global wait_timeout = 28800; set global interactive_timeout = 28800;
That the set value has not been reset, it needs to be specified in the file /etc/mysql/my.cnf, in the mysqld block:
[mysqld] wait_timeout = 28800 interactive_timeout = 28800
connect_timeout – the number of seconds that the mysql server waits for the connection package before terminating the connection.
Connect to MySQL and see the current value:
mysql -u USER -p show variables like "connect_timeout"; quit;
The value of connect_timeout can be specified in the file /etc/mysql/my.cnf, for example:
In real time, you can change by executing the SQL query (after restarting MySQL it will be reset to the standard or specified in the configuration file):
SET GLOBAL connect_timeout=10;
The standard value is 10, the minimum value is 2, the maximum is 31536000.