Я хочу создать резервную копию только представлений с помощью mysqldump.
Возможно ли это?
Если да, то как?
Резервное копирование представлений с помощью Mysql Dump
Ответ 1
ПРИМЕЧАНИЕ. Этот ответ от Ken переместился из предлагаемого редактирования в собственный ответ.
здесь полный пример командной строки, использующий вариант выше
mysql -u username INFORMATION_SCHEMA
--skip-column-names --batch
-e "select table_name from tables where table_type = 'VIEW'
and table_schema = 'database'"
| xargs mysqldump -u username database
> views.sql
Это извлекает все имена представлений через запрос в базу данных INFORMATION_SCHEMA, а затем передает их в xargs для формулировки команды mysqldump. --skip-column-names и -batch необходимы, чтобы сделать выходные xargs дружественными. Эта команда может занять слишком много времени, если у вас много просмотров, и в этом случае вам нужно добавить какой-то дополнительный фильтр к выбору (например, искать все представления, начинающиеся с заданного символа).
Ответ 2
Я изменил замечательный ответ Andomar, чтобы дать возможность базе данных (и другим настройкам) указываться только один раз:
#!/bin/bash -e
mysql --skip-column-names --batch -e \
"select table_name from information_schema.views \
where table_schema = database()" $* |
xargs --max-args 1 mysqldump $*
Я сохраняю это как mysql-dump-views.sh и вызываю его через:
$ mysql-dump-views.sh -u user -ppassword databasename >dumpfile.sql
Ответ 3
Резервное копирование представлений нескольких баз данных легко, просто используя information_schema:
mysql --skip-column-names --batch -e 'select CONCAT("DROP TABLE IF EXISTS ", TABLE_SCHEMA, ".", TABLE_NAME, "; CREATE OR REPLACE VIEW ", TABLE_SCHEMA, ".", TABLE_NAME, " AS ", VIEW_DEFINITION, "; ") table_name from information_schema.views'
Ответ 4
Посредством резервного копирования, я предполагаю, что вы имеете в виду только определение без данных.
Кажется, что сейчас mysqldump не различает VIEW и TABLEs, поэтому, возможно, лучше всего либо указать VIEWs явно в командной строке на mysqldump, либо вычислять этот список динамически перед mysqldump, а затем передавать его вниз, как раньше.
Вы можете получить все VIEW в конкретной базе данных, используя этот запрос:
SHOW FULL TABLES WHERE table_type='view';
Ответ 5
Я хотел бы как можно ближе подойти к выводу mysqldump, как запросил OP, поскольку он включает в себя множество информации о представлении, которое невозможно восстановить с помощью простого запроса из INFORMATION_SCHEMA.
Вот как я создаю представление развертывания script из моей исходной базы данных:
SOURCEDB="my_source_db"
mysql $SOURCEDB --skip-column-names -B -e \
"show full tables where table_type = 'view'" \
| awk '{print $1}' \
| xargs -I {} mysqldump $SOURCEDB {} > views.sql
Ответ 6
С точки зрения ответа на этот вопрос, olliiiver ответ лучше всего делать это напрямую. В качестве ответа я попытаюсь встроить это в комплексное решение для полного резервного копирования и восстановления.
С помощью других ответов на этот вопрос и нескольких других ресурсов я придумал этот сценарий для простой замены базы данных на моем сервере разработки оперативной копией с рабочего сервера по требованию. Он работает на одной базе данных за раз, а не на всех базах данных. Хотя у меня есть отдельный скрипт для этого, здесь небезопасно делиться, так как он в основном отбрасывает и воссоздает все, кроме нескольких избранных баз данных, и ваша среда может отличаться.
Сценарий предполагает наличие корневой системы и пользователя MySQL на обеих машинах (хотя это можно изменить), работает SSH без пароля между серверами и использует файл паролей MySQL/root/mysqlroot.cnf на каждой машине, который выглядит следующим образом:
[client]
password=YourPasswordHere
Файл: synctestdb.sh, необязательно, по символической ссылке на /usr/sbin/synctestdb для простоты использования
Usage: synctestdb DBNAME DESTSERVER
Запустите его с рабочего сервера.
Вот:
#!/bin/bash
if [ "${1}" != "" ] && [ "${1}" != "--help" ] && [ "${2}" != "" ] ; then
DBNAME=${1}
DESTSERVER=${2}
BKDATE=$( date "+%Y-%m-%d" );
SRCHOSTNAME=$( /bin/hostname )
EXPORTPATH=/tmp
EXPORTFILE=/tmp/${SRCHOSTNAME}_sql_${BKDATE}_devsync.sql
CREDSFILE=/root/mysqlroot.cnf
SSHUSER=root
DBEXISTS=$( echo "SHOW DATABASES LIKE '${DBNAME}'" \
| mysql --defaults-extra-file=${CREDSFILE} -NB INFORMATION_SCHEMA )
if [ "${DBEXISTS}" == "${DBNAME}" ] ; then
echo Preparing --ignore-tables parameters for all relevant views
echo
#build --ignore-table parameters list from list of all views in
#relevant database - as mysqldump likes to recreate views as tables
#we pair this with an export of the view definitions later below
SKIPVIEWS=$(mysql --defaults-extra-file=${CREDSFILE} \
-NB \
-e "SELECT \
CONCAT( '--ignore-table=', TABLE_SCHEMA, '.', TABLE_NAME ) AS q \
FROM INFORMATION_SCHEMA.VIEWS \
WHERE TABLE_SCHEMA = '${DBNAME}';" )
if [ "$?" == "0" ] ; then
echo Exporting database ${DBNAME}
echo
mysqldump --defaults-extra-file=${CREDSFILE} ${SKIPVIEWS} \
--add-locks --extended-insert --flush-privileges --no-autocommit \
--routines --triggers --single-transaction --master-data=2 \
--flush-logs --events --quick --databases ${DBNAME} > ${EXPORTFILE} \
|| echo -e "\n\nERROR: ${SRCHOSTNAME} failed to mysqldump ${DBNAME}"
echo Exporting view definitions
echo
mysql --defaults-extra-file=${CREDSFILE} \
--skip-column-names --batch \
-e "SELECT \
CONCAT( \
'DROP TABLE IF EXISTS ', TABLE_SCHEMA, '.', TABLE_NAME, \
'; CREATE OR REPLACE VIEW ', TABLE_SCHEMA, '.', TABLE_NAME, ' AS ', \
VIEW_DEFINITION, '; ') AS TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS \
WHERE TABLE_SCHEMA = '${DBNAME}';" >> ${EXPORTFILE} \
|| echo -e "\n\nERROR: ${SRCHOSTNAME} failed to mysqldump view definitions"
echo Export complete, preparing to transfer export file and import
echo
STATUSMSG="SUCCESS: database ${DBNAME} synced from ${SRCHOSTNAME} to ${DESTSERVER}"
scp \
${EXPORTFILE} \
${SSHUSER}@${DESTSERVER}:${EXPORTPATH}/ \
|| STATUSMSG="ERROR: Failed to SCP file to remote server ${DESTSERVER}"
ssh ${SSHUSER}@${DESTSERVER} \
"mysql --defaults-extra-file=${CREDSFILE} < ${EXPORTFILE}" \
|| STATUSMSG="ERROR: Failed to update remote server ${DESTSERVER}"
ssh ${SSHUSER}@${DESTSERVER} \
"rm ${EXPORTFILE}" \
|| STATUSMSG="ERROR: Failed to remove import file from remote server ${DESTSERVER}"
rm ${EXPORTFILE}
echo ${STATUSMSG}
else
echo "ERROR: could not obtain list of views from INFORMATION_SCHEMA"
fi
else
echo "ERROR: specified database not found, or SQL credentials file not found"
fi
else
echo -e "Usage: synctestdb DBNAME DESTSERVER \nPlease only run this script from the live production server\n"
fi
Пока что это работает, хотя вы можете настроить его для своих целей. Убедитесь, что где бы ни находился ваш файл учетных данных, он имеет защищенные права доступа, чтобы неавторизованные пользователи не могли его прочитать!
Поскольку кажется, что экспорт должным образом трудно экспортировать, я адаптировал ответ olliiiver таким образом, чтобы сначала мы удалили любые таблицы или представления с точными именами допустимых представлений в базе данных, в которую мы импортируем, если они существуют, а затем импортировали все таблицы, который может ошибочно создать эти представления в виде таблиц, затем удалить эти таблицы и правильно определить эти представления.
В основном вот как это работает:
- проверить существование базы данных, указанной вами в командной строке
- используйте MYSQLDUMP для создания файла дампа
- SCP файл дампа с производства на указанный тестовый сервер
- выдает команды импорта на указанном тестовом сервере через SSH и возвращает результат
- удалить файл дампа с обоих серверов после завершения
- выдать разумный вывод для большинства шагов на этом пути
Ответ 7
Спасибо за это - очень полезно.
Одна икота, хотя - возможно, поскольку у меня есть немного запутанный набор представлений, которые ссылаются на другие виды и т.д.:
Я обнаружил, что пользователь "определителя" должен существовать и иметь правильные разрешения для целевой схемы, иначе mysql не будет генерировать представления, ссылающиеся на другие представления, поскольку в нем недостаточно определений.
В сгенерированном:
/*! 50013 DEFINER = <user> @<host> ОПРЕДЕЛЕНИЕ БЕЗОПАСНОСТИ SQL */
- > обеспечить <user> @<host> нормально в вашем целевом экземпляре или заменить эту строку на пользователя, который это делает.
Спасибо Торстейн