Bureaucrats, confirmed, Administrators
4,558
edits
Changes
Jump to navigation
Jump to search
no edit summary
mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | mysql
</source>
== MariaDB Differences between Ubuntu and Debian ==
https://mariadb.com/kb/en/library/differences-in-mariadb-in-debian-and-ubuntu/
If you're collation and character sets are all latin1_swedish, but you want them to be UTF-8, then you can set it in the configuration file, and restart the database.
<source lang="mysql">
show GLOBAL VARIABLES LIKE 'character_set_%';
show VARIABLES LIKE 'collation%';
</source>
<pre>
# Variable_name, Value
'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'
</pre>
This is the mess that I wound up with somehow:
<source lang="mysql">
SET @DATABASE_NAME = 'wiki_freephile';
SELECT
TABLE_SCHEMA,
COUNT(COLLATION_NAME) AS 'count',
COLLATION_NAME
FROM
information_schema.columns AS cols
WHERE
table_schema = @DATABASE_NAME
GROUP BY COLLATION_NAME;
</source>
<pre>
wiki_freephile, 0,
wiki_freephile, 11, binary
wiki_freephile, 73, latin1_bin
wiki_freephile, 69, latin1_swedish_ci
wiki_freephile, 1, utf8_general_ci
</pre>
So, I fixed the defaults for each database:
<source lang="mysql">
ALTER DATABASE wiki_freephile CHARACTER SET = 'binary' COLLATE = 'BINARY';
ALTER DATABASE wiki_meta CHARACTER SET = 'binary' COLLATE = 'BINARY';
ALTER DATABASE wiki_wiki CHARACTER SET = 'binary' COLLATE = 'BINARY';
</source>
You can check the status of each table too:
<source lang="mysql">
SET @DATABASE_NAME = 'wiki_freephile';
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.tables
WHERE TABLE_SCHEMA = @DATABASE_NAME
AND TABLE_COLLATION not in ('binary');
</source>
To set the mysql server to utf8, add the following to <code>/etc/mysql/my.cnf</code> (Debian/Ubuntu) or <code>/etc/my.cnf</code> (Fedora/Centos/RHEL) under the '''<nowiki>[mysqld]</nowiki>''' section:
<pre>
character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake
</pre>
Then restart mysql/mariadb:
*<code>service mariadb restart</code> for Debian/Ubuntu;
*<code>/bin/systemctl restart mariadb.service</code> for Fedora/Centos/RHEL
<ref>https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/</ref>
== Working at the Console ==
Reading output from the mysql command line client is notoriously ugly/hard. With the <code>-s --silent</code> or <code>-B --batch</code> options, you can get output that is more readable.
== Tools ==
