You are here:

Estimated reading time: 4 min

In this article

Find and replace text

UPDATE `table_name`  SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

Source

SSH DB Tunnel

ssh -L {{puerto a usar}}:localhost:{{puerto a escuchar}} {{usuario}}@{{ip}} ssh -nNT -L 3307:localhost:3306 adal@162.242.142.135

Look for column

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%clientid%' /*Field*/ and TABLE_SCHEMA = 'banzac_v47' /*DB*/ order by TABLE_NAME

Size of databases

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Source How to determine the size of MySQL databases and tables

MySQL Version:

SHOW VARIABLES LIKE "%version%";

List users

SELECT User,Host FROM mysql.user;

Create user

CREATE USER 'name'@'localhost' IDENTIFIED BY 'password'; 
GRANT ALL PRIVILEGES ON `name%` . * TO 'name'@'localhost' WITH GRANT OPTION; 

CREATE USER 'name'@'%' IDENTIFIED BY 'password'; 
GRANT ALL PRIVILEGES ON `name%` . * TO 'name'@'%' WITH GRANT OPTION;

CREATE USER 'leo'@'%' IDENTIFIED BY 'EsXvDC7huQX8gAPYfrt';

GRANT ALL PRIVILEGES ON `leo_%` . * TO 'leo'@'%' WITH GRANT OPTION;

Create Database

CREATE DATABASE tentucom_sufix CHARACTER SET utf8 COLLATE utf8_general_ci;

Give priveleges to specific DB, for user with password

GRANT ALL ON database_name.* to user_name@'%' IDENTIFIED BY 'user_password';

GRANT ALL PRIVILEGES ON ramtulancingo . * TO 'ramtulancingo'@'%';

Remove a MySQL User

DROP USER 'testuser'@'localhost';

Create user, and grant privileges

CREATE USER 'seguicom'@'%' IDENTIFIED BY 'dc94bg8sw0g';

GRANT ALL PRIVILEGES ON `seguicom_%` . * TO 'seguicom'@'%' IDENTIFIED BY 'dc94bg8sw0g';

Create database

Traditional

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

Recomendeded

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Drop Database

DROP DATABASE IF EXISTS TestDb;

Source

Import sql file to new database (sentence by sentence)

use DATABASE_NAME; source path/to/file.sql;

Export Database

You should use mysqldump, since it tries to do the data dump without blocking the database tables.

mysqldump -u [username] -p [database-you-want-to-dump] > [path-to-place-data-dump]

So it could be something like this:

mysqldump -u root -p mydatabase > /home/myuser/database.dump.sql

or like that:

sudo mysqldump mydatabase > /home/myuser/database.dump.sql

Then the database dump would be /home/myuser/database-dump.sql

You can read more about mysqldump here

Source

Remove user

DROP USER 'testuser'@'localhost';

Show/list the users in a MySQL database

select host, user, password from mysql.user;

Source

Create root user (mysql)

CREATE USER 'root'@'%' IDENTIFIED BY '2c663eae631e0d206849f730c772d92207647fd9bae31a50'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION FLUSH PRIVILEGES;

Import MySQL File (.sql) into Server Database (bulk)

Standar

mysql -u {user} -p{password} {db} < {/path/to/file.sql}

When using sudo

sudo mysql {db} < {/path/to/file.sql}

Where {db} is the database name and {/path/to/file.sql} is the path to SQL script file.

With SSL

mysql -h host.ltd -u userDb --ssl-ca=/path/to/crt.pem database_name < /path/to/db.sql -p

Mysql Version

mysql -V

FIX: SQLSTATE[42000]

Syntax error or access violation: 1118 Row size too large. The
maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs

ALTER TABLE `table` ROW_FORMAT=DYNAMIC;

Source

Fix error: Row size too large

SET GLOBAL innodb_strict_mode = 0;

SET innodb_strict_mode = 0;

Source

Fix error: Incorrect datetime value ‘0000-00-00 00:00:00’

SET SQL_MODE='ALLOW_INVALID_DATES';

Source

Turn off ONLY_FULL_GROUP_BY

mysql > SELECT @@sql_mode; mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); mysql > SELECT @@sql_mode;

Source

Initial config Ubuntu on 16.04

Firewall allow $ sudo ufw allow 3306
- https://www.digitalocean.com/community/tutorials/ufw-essentials-common-firewall-rules-and-commands
Annotate bind-addres at \etc\mysql\mysql.conf.d\mysqld.cnf
- ubuntu – 2003 – Can’t connect to MySQL server on…
Allow MySQL Users: Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this…
Restart MySQL: How to restart remote MySQL server running on Ubuntu linux? – Stack Overflow
UFW Essentials: Common Firewall Rules and Commands | DigitalOceanDigitalOcean

Host localhost is not allowed to connect to this MySQL server

Goto your mysql folder and there will be a file called “my.ini“. Simply add this under the mysqld tag skip-grant-tables. Save the file, restart your server.

Login with user and password

mysql -u {user} -p

mysql -u {user} -p[passord]

where {user} is your Database user.

Login with user and password

mysql -u {user} -p

mysql -u {user} -p[passord]

where {user} is your Database user.

Login with sudo

sudo mysql

## No equal comparissons (greater than >, less than <, not equals <>, and BETWEEN)

“`

SELECT column_name, column_name_2

FROM ‘table_name’

WHERE column_name BETWEEN 2 AND 4

“`

## Compare date and text (surround them with single quotes)

“`

SELECT *

FROM column_name

WHERE column_name ‘2013-01-01’

“`

## Partial search string (% can match any number of unknown characters,_ match a single unknown character)

“`

SELECT *

FROM Customers

WHERE FirstName LIKE ‘Jan%’

“`

## Drop multible tables in DB

“`

DROP TABLE IF EXISTS breeds,cats,users,migrations;

“`

## Set into blank col the id wich is for the name

“`

update capacidads,plantas set capacidads.id_planta=plantas.id where capacidads.nombre_planta=plantas.planta

“`

## Allow remote conection

# See what ports are litening to what (mysql)

$ sudo netstat -plutn | grep 3306

# Make changes in config mysql

—

# skip-external-locking

bind-address = 0.0.0.0

—

# Allow ports Firewall

$ sudo ufw allow out 3306/tcp

$ sudo ufw allow in 3306/tcp

$ sudo ufw status

List Databases

show databases;

Select or choose database

use DATABASE_NAME;

Where DATABASE_NAME is your database’s name.

List or show tables from specific database

show tables from {database};

Where {database} is your databse.

Exit from MySQL console

exit;

A Comprehensive Guide to Using MySQL ENUM

Source

Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client

ALTER USER 'taurus'@'%' IDENTIFIED WITH mysql_native_password BY 'password6b78yn';

Source

MySQL Workbench SSL connection error: SSL is required but the server doesn’t support it

This answer helped me in Workbench 8.0 https://dba.stackexchange.com/a/303329

Basically, create new connection using the advanced tab by entering “useSSL=0” in the ‘Others’ tab.

Source

How do I fix the error “Mysql Server has gone away”?

The MySQL server has gone away (error 2006) has two main causes and solutions:

Server timed out and closed the connection. To fix, check that wait_timeout mysql variable in your my.cnf configuration file is large enough, eg wait_timeout = 28800
You may also need to increase the innodb_log_file_size mysql variable in your my.cnf configuration to for example innodb_log_file_size = 128MB or higher.
Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. To fix, you can increase the maximal packet size limit max_allowed_packet in my.cnf file, eg. set max_allowed_packet = 128M, then restart your MySQL server: sudo /etc/init.d/mysql restart
Database server ran out of space. This can occur when performing an update to the Matomo database that requires DB Schema changes (Especially for large databases).

Once you’ve made these changes, and restarted your MySQL or MariaDB server, the issue should be fixed and no more error triggered. If not, try increase the 128M values to 256M for example. In websites with a lot of traffic and data, you might even need to increase the value to 1024M.

If the above changes do not resolve the issue and if you are using MYSQLI adapter in Matomo, try switching to adapter = PDO\MYSQL in your config/config.ini.php file.

Source

Was this article helpful?

Like 1 Dislike 0