For most database work I use phpMyAdmin. However, on the rare occasion I encounter a strange error — looking at your foreign key constraints — I opt for the Windows command prompt. I do this because a) troubleshooting database issues is far outside of my skillset and b) I need to get stuff done regardless of that. Also, the errors in phpMyAdmin typically don’t exist when performing the same task in the Windows command prompt (for the commands I run).
Dumping and Restoring MySQL databases on the Windows command prompt is a simple process.
Using MYSQL with MAMP. Step 1: Start MAMP which will display this window.Click on the middle button to bring up the Start Page. Step 2: The mac and PC version of this page may look different but both have the same link under the MySQL section in the middle. So I copied the mysqldump command and copied it to the command line and hit enter. Mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect. After this I tried several methods to solve the socket issue after I found the solution from www.razorsql.com. Mysqldump command is used to take backup of the database. I faced an issue of empty backup file creation while running mysqldump command through php script. To debug this issue following steps are there: 1) Search mysqldump.exe or simply mysqldump in your WAMP, MAMP, LAMP, XAMPP etc directory.
Fire up your Windows command prompt. In Windows 7, click the start orb and type cmd in the Search Programs and Files input and hit enter.
If MySQL has been added to your Windows path variable, continue to step 3. If this is the first time you have run a
mysqlcommand in the Windows command prompt, it will return the error captured below.Fear not, your computer just needs to know the location of MySQL to run MySQL-related commands. You tell Windows this location by adding the MySQL directory to your path variable. I covered how to add PHP to your Windows path variable using the Windows GUI in a different post. But in the spirit of going commando, let’s add MySQL’s location to the Windows path variable using the command prompt.
In your Windows command prompt, execute the command:
set path=C:pathtomysqlexecutable, where C:pathtomysqlexecutable corresponds to the location of the mysql.exe file on your machine. I am running MySQL version 5.6.12, which was bundled with WAMP at my time of my download. So, the MySQL executable is located inC:wampbinmysqlmysqlmysql5.6.12bin.After you have added MySQL’s location to your Windows path variable, you should test the
mysqlcommand. Below is a screenshot of MySQL being added to the Windows path variable and themysqlcommand tested on my machine.Now that
mysqlis recognized in the Windows command prompt, you can run the MySQL commands listed below to dump and restore your databases.
To dump/export a MySQL database, execute the following command in the Windows command prompt:
mysqldump -u username -p dbname > filename.sql. After entering that command you will be prompted for your password. Once the password is entered your dump file will be available in the root directory for your Windows user – ie:C:Usersusername. An example is shown in the screenshot below.To restore/import a MySQL database, execute the following command in the Windows command prompt:
mysql -u username -p dbname < filename.sql. After entering that command you will be prompted for your password. Once the password is entered your dump file that exists in the root directory for your Windows user will be restored. An example is shown in the screenshot below.
That's it! We have successfully dumped and restored a MySQL database using the Windows command prompt.
| <?php |
| /** |
| * Yii Application Config |
| * File: /config/app.php OR /config/app.web.php |
| * |
| * This is an example of how to enable the MAMP helper module. |
| */ |
| return [ |
| 'modules' => [ |
| 'mamp-helper' => modulesMampHelper::class, |
| ], |
| 'bootstrap' => ['mamp-helper'], |
| ]; |
Mamp Mysqldump Php
| <?php |
| namespace modules; |
| useCraft; |
| /** |
| * MAMP Helper class. |
| * File: /modules/MampHelper.php |
| * |
| * MySQL database backups triggered from the Craft 3 Control Panel fail because, |
| * by default, Craft expects to use `mysqldump` in an `exec()` command. In MAMP, |
| * `mysqldump` is not available to PHP in the `PATH` env variable. A few Google |
| * searches will surface some solutions that work for some people, not for others. |
| * |
| * Craft provides two configuration options to help with this: `backupCommand` |
| * and `restoreCommand`. With these you can add your own commands and use tokens |
| * for parameters like `{password}` for the database password or `{file}` for |
| * the destination file in the `/storage/backups` directory. These commands |
| * will override Craft's default commands, but using them requires specific |
| * knowlege about MySQL command line usage. |
| * |
| * This module helps by taking Craft's default commands, altering them to point |
| * to MAMP's executables (`msyql` and `mysqldump`), then adds them to the |
| * config under the appropriate keys, `backupCommand` and `restoreCommand`. |
| * |
| * Drop this file in your `/modules` directory. Then in `/config/app.php` |
| * (or `/config/web.app.php` if you prefer) make it look like this: |
| * |
| * ``` |
| * return [ |
| * 'modules' => [ |
| * 'mamp-helper' => modulesMampHelper::class, |
| * ], |
| * 'bootstrap' => ['mamp-helper'], |
| * ]; |
| * ``` |
| * |
| * Be sure not to step on previously existing modules, of course. |
| * Now you should be able to upgrade with backups, restore, and generate |
| * backups without MAMP hacks. |
| */ |
| classMampHelperextends yiibaseModule |
| { |
| /** |
| * Initializes the module. |
| */ |
| publicfunctioninit() |
| { |
| parent::init(); |
| // Only on CP request, using mysql, and MAMP exists |
| if ( |
| Craft::$app->request->getIsCpRequest() && |
| Craft::$app->config->db->driver'mysql' && |
| file_exists('/Applications/MAMP/Library/bin') |
| ) { |
| // Set the general config 'backupCommand' to Craft's own |
| // default backup command, just with the full path to |
| // MAMP's `mysqldump` executable. |
| Craft::$app->config->general->backupCommand = str_replace( |
| 'mysqldump', |
| '/Applications/MAMP/Library/bin/mysqldump', |
| Craft::$app->db->getSchema()->getDefaultBackupCommand() |
| ); |
| // Set the general config 'restoreCommand' to Craft's own |
| // default restore command, just with the full path to |
| // MAMP's `mysql` executable. |
| Craft::$app->config->general->restoreCommand = str_replace( |
| 'mysql', |
| '/Applications/MAMP/Library/bin/mysql', |
| Craft::$app->db->getSchema()->getDefaultRestoreCommand() |
| ); |
| } |
| } |
| } |
Mamp Mysqldump Pro