Synchronization of 2 MySQL databases in PHP
Red6Pc
-
Red6Pc -
Red6Pc -
Bonjour, pour exporter votre base de données MySQL depuis un serveur local Windows Server 2012 R2 vers un serveur en ligne chez IONOS, vous pouvez suivre les étapes ci-dessous :
1. **Exporter la base de données locale :**
- Ouvrez l'invite de commande.
- Utilisez la commande suivante pour exporter votre base de données :
```bash
mysqldump -u [utilisateur] -p[password] [nom_base_locale] > C:\chemin\vers\export.sql
```
- Remplacez `[utilisateur]`, `[password]`, et `[nom_base_locale]` par vos informations. Le fichier `export.sql` contiendra votre base de données exportée.
2. **Transférer le fichier exporté :**
- Utilisez un client FTP (comme FileZilla) pour transférer le fichier `export.sql` vers votre serveur IONOS.
3. **Créer la base de données sur le serveur IONOS :**
- Connectez-vous à votre panel IONOS et créez une nouvelle base de données avec la même structure que votre base locale, mais avec un nom différent.
4. **Importer le fichier SQL :**
- Connectez-vous à votre serveur IONOS via SSH (si disponible) ou via phpMyAdmin.
- Si vous utilisez SSH, exécutez la commande suivante pour importer le fichier :
```bash
mysql -u [utilisateur] -p[password] [nom_base_IONOS] < /chemin/vers/export.sql
```
### Pour la seconde étape
1. **Écrire le script PHP :**
- Créez un script PHP sur votre serveur IONOS qu'il importe les données de `export.sql`.
- Exemple de code :
```php
<?php
$servername = "localhost";
$username = "[utilisateur]";
$password = "[password]";
$dbname = "[nom_base_IONOS]";
// Créer une connexion
$conn = new mysqli($servername, $username, $password, $dbname);
// Vérifier la connexion
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Exécuter le fichier SQL
$sql = file_get_contents('/chemin/vers/export.sql');
if ($conn->multi_query($sql)) {
echo "Importation réussie.";
} else {
echo "Erreur lors de l'importation : " . $conn->error;
}
$conn->close();
?>
```
2. **Exécuter le script :**
- Accédez à votre script PHP via le navigateur pour lancer l'importation.
Ces étapes devraient vous permettre d'exporter et d'importer vos bases de données avec succès.
1. **Exporter la base de données locale :**
- Ouvrez l'invite de commande.
- Utilisez la commande suivante pour exporter votre base de données :
```bash
mysqldump -u [utilisateur] -p[password] [nom_base_locale] > C:\chemin\vers\export.sql
```
- Remplacez `[utilisateur]`, `[password]`, et `[nom_base_locale]` par vos informations. Le fichier `export.sql` contiendra votre base de données exportée.
2. **Transférer le fichier exporté :**
- Utilisez un client FTP (comme FileZilla) pour transférer le fichier `export.sql` vers votre serveur IONOS.
3. **Créer la base de données sur le serveur IONOS :**
- Connectez-vous à votre panel IONOS et créez une nouvelle base de données avec la même structure que votre base locale, mais avec un nom différent.
4. **Importer le fichier SQL :**
- Connectez-vous à votre serveur IONOS via SSH (si disponible) ou via phpMyAdmin.
- Si vous utilisez SSH, exécutez la commande suivante pour importer le fichier :
```bash
mysql -u [utilisateur] -p[password] [nom_base_IONOS] < /chemin/vers/export.sql
```
### Pour la seconde étape
1. **Écrire le script PHP :**
- Créez un script PHP sur votre serveur IONOS qu'il importe les données de `export.sql`.
- Exemple de code :
```php
<?php
$servername = "localhost";
$username = "[utilisateur]";
$password = "[password]";
$dbname = "[nom_base_IONOS]";
// Créer une connexion
$conn = new mysqli($servername, $username, $password, $dbname);
// Vérifier la connexion
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Exécuter le fichier SQL
$sql = file_get_contents('/chemin/vers/export.sql');
if ($conn->multi_query($sql)) {
echo "Importation réussie.";
} else {
echo "Erreur lors de l'importation : " . $conn->error;
}
$conn->close();
?>
```
2. **Exécuter le script :**
- Accédez à votre script PHP via le navigateur pour lancer l'importation.
Ces étapes devraient vous permettre d'exporter et d'importer vos bases de données avec succès.
6 answers
Hello,
For MySQL databases... you just need to make a DUMP of the one you want to back up, then import that DUMP into your new database
https://phoenixnap.com/kb/how-to-backup-restore-a-mysql-database
https://www.sqlshack.com/how-to-backup-and-restore-mysql-databases-using-the-mysqldump-command/
.
--
Best regards,
Jordane
For MySQL databases... you just need to make a DUMP of the one you want to back up, then import that DUMP into your new database
https://phoenixnap.com/kb/how-to-backup-restore-a-mysql-database
https://www.sqlshack.com/how-to-backup-and-restore-mysql-databases-using-the-mysqldump-command/
.
--
Best regards,
Jordane
Je suis tout à fait d'accord avec vous sur ce point (c'est-à-dire "In Command Line"), only that in my case the problem is to write a PHP script that should first EXPORT my entire Database locally, then connect to the Online MySQL Server of IONOS to do the opposite, that is to say, IMPORT the said MySQL Database which has the same structure ... How to write this PHP script??? Because I am not sure that the syntax of the Code that works in Command Line is the same as that to be written in a PHP script!!! These are two totally different environments. Furthermore, this script must be automated, I should create a CRON task on my Windows Server to do it automatically, of course at regular intervals, like every 3 hours (at 6:30, then at 9:30, then again at 12:30, 15:30, 18:30, 20:30 and it should stop at 23:30 for example ...).
This is exactly what it's all about in detail.
This is exactly what it's all about in detail.
Then connect to the MySQL Online Server of IONOS
Already... this point may cause a blockage...
Remote access to your database (on an IONOS server) is, most often, impossible.
These are two completely different environments. Additionally, this script needs to be automated; I should create a CRON task on my Windows Server to do it automatically, of course at regular intervals, like every 3 hours (at 6:30, then at 9:30, again at 12:30, 15:30, 18:30, 20:30, and it should stop at 23:30 for example...).
Through this CRON task, generate a dump of your database... then send the dump via FTP to your IONOS server
Then, on your IONOS server, also create a CRON task that would load this dump into your database.
I want to say that the syntax of mysqldump is only suitable for the command line and not in a PHP script, because I set up this script and configured a scheduled task on Windows Server 2012 R2 (CRON task) and going into the system, the Windows Task Scheduler indicated a message: "The operation was canceled by the user. (0x800704C7) ...
Here is the content of my PHP script that I have logged in a folder of my application:
I do not see where the problem is in my code...
Here is the content of my PHP script that I have logged in a folder of my application:
//Enter your database information and backup file name here. $mysqlDatabaseName ='database'; $mysqlUserName ='xxxxxxxxxxxxxx'; $mysqlPassword ='xxxxxxxxxxxxxx'; $mysqlHostName ='ip address'; $mysqlExportPath ='export-file.sql'; //Please do not modify the following points //Database export and result $command='mysqldump --opt -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ' .$mysqlExportPath; exec($command,$output=array(),$worked); switch($worked){ case 0: echo 'The database <b>' .$mysqlDatabaseName .'</b> has been successfully stored at the following path '.getcwd().'/' .$mysqlExportPath .'</b>'; break; case 1: echo 'An error occurred while exporting <b>' .$mysqlDatabaseName .'</b> to '.getcwd().'/' .$mysqlExportPath .'</b>'; break; case 2: echo 'An export error occurred, please check the following information: <br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr></table>'; break; } I do not see where the problem is in my code...
Given the error message, it seems more like an execution problem of the script from the task scheduler.
Have you at least tested the script directly.... (without going through the scheduler)?
And how did you create your scheduled task?
Which user did you use to schedule this task?
Generally, we execute the PHP script from a bat file... and it's this bat that is launched by Windows... and the PATH to the PHP exe needs to be configured in the system
(but here, we are going beyond the scope of this forum which only deals with the PHP language... if you have issues scheduling the task, you will certainly need to ask a question in the Windows forum.)
Note that you could also generate your dump in PowerShell ...
for example
https://gallery.technet.microsoft.com/scriptcenter/PowerShell-to-perform-a687f0df
Have you at least tested the script directly.... (without going through the scheduler)?
And how did you create your scheduled task?
Which user did you use to schedule this task?
Generally, we execute the PHP script from a bat file... and it's this bat that is launched by Windows... and the PATH to the PHP exe needs to be configured in the system
(but here, we are going beyond the scope of this forum which only deals with the PHP language... if you have issues scheduling the task, you will certainly need to ask a question in the Windows forum.)
Note that you could also generate your dump in PowerShell ...
for example
https://gallery.technet.microsoft.com/scriptcenter/PowerShell-to-perform-a687f0df
Following your link https://gallery.technet.microsoft.com/scriptcenter/PowerShell-to-perform-a687f0df, I did not see how they explain generating the DUMP in PowerShell ...
To answer your questions, I would say:
1/- How can I test the script directly without going through the scheduler?
2/- For creating my scheduled task, I simply followed this tutorial via this link https://www.pcastuces.com/pratique/astuces/4245.htm
3/- Which user are you referring to? I specified at the beginning of my PHP script the name of the user I created in phpMyAdmin, as well as their password, like this:
4/- How do you run a PHP script from a bat file?
To answer your questions, I would say:
1/- How can I test the script directly without going through the scheduler?
2/- For creating my scheduled task, I simply followed this tutorial via this link https://www.pcastuces.com/pratique/astuces/4245.htm
3/- Which user are you referring to? I specified at the beginning of my PHP script the name of the user I created in phpMyAdmin, as well as their password, like this:
$mysqlDatabaseName ='database name'; $mysqlUserName ='database user name'; $mysqlPassword ='user password'; $mysqlHostName ='server IP address'; $mysqlExportPath ='export-file.sql';
4/- How do you run a PHP script from a bat file?
In contrast, still in my search for solutions, I found a script on GitHub that allows you to export a database via PHP and save it via FTP, but I haven't tried it yet. Here is the complete PHP script organized into 2 files:
A/ - PHP Script No. 1:
B/ - PHP Script No. 2:
Unlike the first, this script seems much more coherent to me... It's true that it's a bit longer... What do you think?
A/ - PHP Script No. 1:
/** * DatabaseExport is a tool to export database via PHP and store it via FTP * * Copyright (c) 2011 Tomas Pavlatka (http://tomas.pavlatka.cz) * * @package DatabaseExport */ header("content-type: text/plain; charset=utf-8"); // Require classes. require_once 'classes/Database.php'; // Export params. $params = array( 'database' => array( 'db_host' => 'localhost', 'db_name' => 'db_name', 'db_user' => 'root', 'db_password' => '', 'db_charset' => 'utf8'), 'ftp' => array( 'ftp_server' => '', 'ftp_username' => '', 'ftp_password' => '', 'ftp_folder' => '' ), 'export' => array( 'export_folder' => './backups/', 'export_zip' => 'bzip2', ) ); // Export database.. $dbObj = new PTX_Database($params['database']); $dbObj->connect(); $dbTables = array(); if($dbObj->mysqlListTables()) { while($table = $dbObj->mysqlFetchArray()){ $dbTables[] = (string)$table[0]; } } $exportedData = $dbObj->dbExport($dbTables); // Save a file. $fileName = $params['database']['db_name'].'-'.date('YmdHis').'.sql'; $filePath = $params['export']['export_folder'].$fileName; if($params['export']['export_zip'] == 'bzip2') { $fileName .= '.bz2'; $filePath .= '.bz2'; $bz = bzopen($filePath, "w"); bzwrite($bz, $exportedData); bzclose($bz); } else { $fopen = fopen($filePath,'w+'); fwrite($fopen,$exportedData); fclose($fopen); } // Copy to FTP. $ftpConnect = ftp_connect($params['ftp']['ftp_server']); $ftpLogin = ftp_login($ftpConnect, $params['ftp']['ftp_username'], $params['ftp']['ftp_password']); if($ftpLogin) { // Upload a file $destination = $params['ftp']['ftp_folder'].$fileName; $upload = ftp_put($ftpConnect, $destination, $filePath, FTP_BINARY); } ftp_close($ftpConnect); B/ - PHP Script No. 2:
/** * DatabaseExport is a tool to export database via PHP and store it via FTP * * Copyright (c) 2011 Tomas Pavlatka (http://tomas.pavlatka.cz) * * @package DatabaseExport */ class PTX_Database { /* * Holds actual connection. */ private $_dbConnect; /* * Private variable holding option for class. */ private $_options = array( 'db_host' => '', 'db_name' => '', 'db_user' => '', 'db_password' => '', 'db_charset' => 'utf-8'); private $_selectQuery; /** * Construct. * * constructor of the class * @param array $options - parameters for class */ public function __construct(array $options = array()) { $this->_options = array_merge($this->_options,$options); } /** * Connect. * * connects to the database. */ public function connect(){ if(!$this->_dbConnect = @mysql_connect($this->_options['db_host'],$this->_options['db_user'],$this->_options['db_password'])){ exit('Unable to connect to database'); } if(!@mysql_select_db($this->_options['db_name'], $this->_dbConnect)) { exit(sprintf('Unable to connect to database %s',$this->_options['db_name'])); } // Set charset. mysql_query("SET CHARACTER SET ".$this->_options['db_charset']); } /** * Export. * * exports database into text * @param array $tables - list of the table * @return exported data */ public function dbExport(array $tables) { $exportData = null; foreach($tables as $table) { $exportData .= $this->_createExportHeader($table); $exportData .= $this->_getExportData($table); } return $exportData; } /** * Mysql Fetch Array. * * fetch array from database. */ public function mysqlFetchArray(){ return @mysql_fetch_array($this->_selectQuery); } /** * Mysql Fetch Assoc. * * fetch array assoc from database. */ public function mysqlFetchAssoc(){ return @mysql_fetch_assoc($this->_selectQuery); } /** * Mysql List Tables. * * mysql_query to find all tables in database. * @return true | false */ public function mysqlListTables(){ if(!$this->_selectQuery = @mysql_query("SHOW TABLES FROM ".$this->_options['db_name'],$this->_dbConnect)) { return false; } else { return true; } } /** * Create export header (Private). * * creates header for export * @param $table - name of a table * @return header */ private function _createExportHeader($table) { // Fields. $fields = array(); $query = mysql_query("DESCRIBE `{$table}`",$this->_dbConnect); while($field = mysql_fetch_assoc($query)) { $fields[] = $field; } // Indexes. $indexes = array(); $query = mysql_query("SHOW INDEXES FROM `{$table}`"); while($index = mysql_fetch_assoc($query)) { if(isset($indexes[$index['Key_name']])) { $indexes[$index['Key_name']][] = $index; } else { $indexes[$index['Key_name']] = array($index); } } // Table status. $query = mysql_query("SHOW TABLE STATUS WHERE `Name` = '{$table}'"); $status = mysql_fetch_assoc($query); // Table header. $header = "DROP TABLE IF EXISTS `{$table}`;\n"; $header .= "CREATE TABLE `{$table}` (\n"; foreach($fields as $key => $values) { $header .= "\t`".$values['Field']."` ".$values['Type']; if($values['Null'] == 'NO') { $header .= " not null"; } else { $header .= " null"; } // TODO: What about MySQL constants ? if(!empty($values['Default'])) { if($values['Default'] == 'CURRENT_TIMESTAMP') { $header .= " default ".$values['Default']; } else { $header .= " default '".$values['Default']."'"; } } if(!empty($values['Extra'])) { $header .= " ".$values['Extra']; } $header .= ",\n"; } $countIndexes = count($indexes); $counter = 1; foreach($indexes as $indexKey => $indexValeus) { if($indexKey == 'PRIMARY') { $header .= "\tPRIMARY KEY ("; $keyString = null; foreach($indexValeus as $indKey => $indValues) { $keyString .= "`".$indValues['Column_name']."`,"; } $header .= substr($keyString,0,-1); } else { $header .= "\tKEY `".$indexKey."` ("; $keyString = null; foreach($indexValeus as $indKey => $indValues) { $keyString .= "`".$indValues['Column_name']."`"; if(!empty($indValues['Sub_part'])) { $keyString .= ' ('.$indValues['Sub_part'].')'; } $keyString .= ","; } $header .= substr($keyString,0,-1); } if($counter++ < $countIndexes) { $header .= "),\n"; } else { $header .= ")\n"; } } $header .= ") "; // Additional information. $charsetExplode = explode('_',$status['Collation']); $status['Charset'] = (isset($charsetExplode[0])) ? $charsetExplode[0] : null; $additionalInfo = array('Engine' => 'ENGINE','Auto_increment' => 'AUTO_INCREMENT', 'Charset' => 'CHARSET', 'Collation' => 'COLLATE'); foreach($additionalInfo as $key => $name) { if(isset($status[$key]) && !empty($status[$key])) { $header .= $name .'='.$status[$key]." "; } } $header .= ";\n\n"; return (string)$header; } /** * Get Export Data (Private). * * exports records from database. * @param $table - name of a table * @return export of records */ private function _getExportData($table) { $d = null; $data = mysql_query("SELECT * FROM `{$table}` WHERE 1", $this->_dbConnect); $counter = 1; $insertData = null; $rowCounter = mysql_num_rows($data); if($rowCounter > 0) { while($row = mysql_fetch_assoc($data)) { if($counter == 1) { $insertData .= "INSERT INTO `{$table}` "; $columns = array_keys($row); $columnNames = null; foreach($columns as $key => $column) { $columnsNames .= "`{$column}`,"; } $insertData .= "(".substr($columnsNames,0,-1).") VALUES \n"; } $insertData .= "("; $rowData = null; foreach($row as $column => $value) { $rowData .= "'".mysql_escape_string($value)."',"; } $insertData .= substr($rowData,0,-1); if($counter < $rowCounter) { $insertData .= "),\n"; } else { $insertData .= ");\n\n"; } // Increase counter; $counter++; } } // Return data. return (string)$insertData; } } Unlike the first, this script seems much more coherent to me... It's true that it's a bit longer... What do you think?
Bonjour, locally as well as on the server, the DUMP of my database runs well via a .bat file in a scheduled task on Windows/Windows Server 2012 R2... For the export, I was testing a procedure with SSH that successfully exported the file on the IONOS server, but when I start the import, I get this message Unknown collation: 'utf8mb4_0900_ai_ci'
I tried to replace it in my exported sql file but nothing works, given that IONOS is on MariaDB and I am on MySQL 8.0.17 - MySQL Community Server - GPL
- Another concern: is there another method in PHP to send the DUMP via FTP to the IONOS server?
I tried to replace it in my exported sql file but nothing works, given that IONOS is on MariaDB and I am on MySQL 8.0.17 - MySQL Community Server - GPL
- Another concern: is there another method in PHP to send the DUMP via FTP to the IONOS server?
Regarding the error message, you should check if you can update the version of MariaDB used on Ionos.
As for performing FTP in PHP, yes, it's definitely possible; there are specific functions for that which you can find in the PHP documentation.
As for performing FTP in PHP, yes, it's definitely possible; there are specific functions for that which you can find in the PHP documentation.
So you agree with me that my version MySQL 8.0.17 - MySQL Community Server - GPL is more up to date than the version MariaDB from IONOS? If so, are you sure that IONOS will allow us to update its database version? Because it seems that when I create my database on IONOS, I must specify utf8_general_ci in order for it to be compatible with my exported DUMP...
Here is what my .bat file looks like that performs the DUMP:
@echo off
mysqldump -h localhost -u Toto -ppassword maBase > C:\folder\loop.sql
And the one that does the FTP
ftp -s:C:\folder\gestion_Extranet.ftp
Here is what my .bat file looks like that performs the DUMP:
@echo off
mysqldump -h localhost -u Toto -ppassword maBase > C:\folder\loop.sql
And the one that does the FTP
ftp -s:C:\folder\gestion_Extranet.ftp
Here is some reading for information:
https://www.ionos.fr/digitalguide/hebergement/aspects-techniques/mariadb-vs-mysql/
If you are on shared hosting, I doubt you can upgrade the version of MariaDB.
You need to make the database that is on your Windows "compatible" with the version of MariaDB present on Ionos.
It starts with modifying the "collation" of the tables to utf8_general_ci (or equivalent) as you have already discovered.
https://www.ionos.fr/digitalguide/hebergement/aspects-techniques/mariadb-vs-mysql/
If you are on shared hosting, I doubt you can upgrade the version of MariaDB.
You need to make the database that is on your Windows "compatible" with the version of MariaDB present on Ionos.
It starts with modifying the "collation" of the tables to utf8_general_ci (or equivalent) as you have already discovered.
Good evening, Yes, it’s true that I won’t be able to update the SGBD version from IONOS, but I can on my PC. I did several tests today via the command line, and indeed it’s my encoding that caused problems... I started from a blank database that I created locally and then online while respecting the UTF-8 encoding, the manual import worked... But another problem arises, that of privileges... Indeed, my local database has a Root with the password "hyper composed" with signs + and others that I set, not knowing the issues it would cause... So even when I did the DUMP with the Root, it didn’t work, I had to create a USER with all the GRANT then the DUMP worked... Now when I import via command line on IONOS, it gives me "ERROR 1227 (42000) Access denied" telling me that I do not have enough privileges... So what should I do in this case?