Stop MySQL: sudo service mysql stop Move (or copy) your mysql directory. If you want /mnt/mysql to be the new directory, do it like this:
.../var/lib/mysql /mnt/ Open your MySQL configuration (sudo vim /etc/mysql/my.cnf) and change the datadir value to your new path (e.g. /mnt/mysql) Modify your AppArmor configuration: sudo vim /etc/apparmor.d/usr.sbin.mysqld Change/copy the lines...
...and their data, under a new name. Make a dump of your source database: mysqldump -uroot -p my_project -r my_project.sql Or, if you only want to dump the database...
...s table structure (schema) without any contents: mysqldump -uroot -p my_project -r my_project.sql --no-data Open up a MySQL shell: mysql -uroot -p From the MySQL shell, create a...
ALTER DATABASE database_name CHARACTER SET "utf8"; ALTER DATABASE database_name COLLATE "utf8_unicode_ci"; After that, for...
A MySQL DECIMAL column is used when it is important to preserve exact precision. It takes two parameters, where precision is the total number of digits and scale the...
...a decimal column definition looks like this: t.decimal :amount, :precision => 6, :scale => 2. Issue MySQL prior to 5.6 stored leading zeros (0003.1) and +/- characters (+2.1) within the column. However, it...
...but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don't represent the actual database sizes? This is for you!
...a mysql root console: SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; This will get you...
MySQL version 5.1 comes with an alternative, faster InnoDB implementation (called "InnoDB Plugin"). Switching is easy: Stop your mysqld with sudo stop mysql Add the following lines to your /etc/mysql/my.cnf...
...under the [mysqld] section ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so Start your mysqld with sudo start mysql The file format has not changed, your tables should survive this.
You will occasionally need to clean out your database while keeping the schema intact, e.g. when someone inserted data in...
This will make MySQL log all received queries so you can see for yourself what happens on the database level. Don't switch this on for production machines!
sudo vim /etc/mysql/my.cnf In the [mysqld] section, add: log=/var/log/mysql.log Restart your MySQL daemon. On Ubuntu: sudo service mysql restart Note that your MySQL performance will suffer. But when...
...with both old and new Rails applications. Switch to MariaDB Remove MySQL: sudo apt remove mysql-common mysql-client mysql-server mysql-apt-config Install MariaDB: sudo apt install mariadb...
The mysql and mysql2 gems installed on your machine are still built against the mysql-client package you just uninstalled, so you will probably get this error when booting...
Unless all MySQL server defaults are set to UTF-8, mysqldump encodes UTF-8 characters incorrectly and only outputs correct UTF-8 when you switch to Latin 1 (!). Also you...
...for each new database. To prevent this, make sure your /etc/mysql/my.cnf looks like this: [mysqld] default-character-set = utf8mb4 collation-server = utf8mb4_unicode_ci [mysql] default-character-set=utf8mb4
If you get a stacktrace complaining about uninitialized constant MysqlCompat::MysqlRes a system library update might broke your gem. You might have switched from MySQL to MariaDB, but forgot to...
...rebuild your MySQL gems. Try fully removing and re-installing the gem: gem uninstall mysql mysql2 bundle install
...when committing a transaction. E.g. you might see an error like this: ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction This has nothing to do with the...
...application-level locks that we talk about below. What happens here is that when MySQL makes changes, it locks rows in the database to ensure that the change will applied...
When you do a script/dbconsole -p, your MySQL shell will already be using UTF-8. When you call it yourself using mysql, it may not be enabled.
...of Hlavní město Praha. You need to manually switch on UTF-8, in the MySQL console: SET NAMES 'utf8...
...to delete rows from a table, and the delete conditions require a joined table, MySQL needs to know which table you want to delete from. Let's say that Post...
You can find out about disk space usage of all tables within your database by running this: SELECT table_name...
...month / 3.0).ceil #=> 1 Yes, you do actually divide by 3.0, not 4.0. MySQL has SELECT QUARTER...
Usually our mysql queries are not case sensitive. In order to query case sensitive, you can use the mysql COLLATE clause. The collate clause lets you specify a collation, which...
In order to open a MySQL shell without the need to enter user and password, you can say the following in any Rails 2 project: script/dbconsole -p
If you have a replication error with MySQL and you know the "error" is okay (e.g. you've executed the same statement at the same time on 2 masters which...
...your second statement is really the next one which would get executed by the MySQL server. Example: you've executed drop database foobar; and drop database blubber; in a row...
Given the problem you have a new column postion and that column should be updated for all existing rows with...
Unless you changed the default, this will be 16 MB: mysql> SHOW VARIABLES WHERE Variable_name="max_allowed_packet"; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | max_allowed_packet...
...server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? Connection to the MySQL database does not work anymore Your travis-ci builds might have started failing on the...
mysql -e 'create database IF NOT EXISTS minidusen_test;' with an error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) The command "mysql...
...on the server (ls -l). In our case, it's 1234567890. Know your (local) MySQL root password. We'll use SECRET below. Know your (local) target database name. For us...
...my_project_development. Go wild: ^ ssh user@example.com "cat /mnt/dumps/my_project.dump.bz2" | pv -s 1234567890 | bzip2 -d | mysql -uroot -pSECRET my_project_development That will: Print the (bzipped) file to stdout on the...
...database server your application runs on. Like MAX or COUNT, GROUP_CONCAT is a MySQL aggregation function you can use whenever your query contains a GROUP BY. You can use...